Re: Capturing milestone data in a table
- Date: Wed, 4 Mar 2015 16:00:40 +0100 (CET)
- From: Johan De Meersman <vegivamp@xxxxxxxxx>
- Subject: Re: Capturing milestone data in a table
----- Original Message -----
> From: "Phil" <pchapman@xxxxxxxxx>
> Subject: Capturing milestone data in a table
> user_credits where metric1 > $mile and (metric1 - lastupdate) < $mile)
That second where condition is bad. Rewrite it as metric1 < ($mile + lastupdate). Better yet, combine them into a between comparison.
As it is, it can't use the index for that because the lefthand is a computed field; the optimiser can only pick an index scan or a full tablescan.
If you keep the lhs expression index-enabled, the optimiser gets the additional option for an index range scan, which is more performant.
It's still not going to be extremely performant, though, because it still has to calculate for each row based on the lastupdate value. Look at converting that to a (pseudo)constant, so only one index scan is needed instead of one per row.
Unhappiness is discouraged and will be corrected with kitten pictures.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql