Re: Should the optimiser convert a CASE into a WHERE if it can?

On Jan 26, 2010, at 9:41 AM, Richard Neill wrote:

> Thanks for your answers.
>
>
> David Wilson wrote:
>
>> Why not simply add the where clause to the original query?
>>
>> SELECT
>> SUM (case when id > 1200000 and id < 1210000 then 1 else 0 end) AS c1,
>> SUM (case when id > 1210000 and id < 1220000 then 1 else 0 end) AS c2,
>> SUM (case when id > 1220000 and id < 1230000 then 1 else 0 end) AS c3,
>> SUM (case when id > 1230000 and id < 1240000 then 1 else 0 end) AS c4,
>> SUM (case when id > 1240000 and id < 1250000 then 1 else 0 end) AS c5
>> FROM tbl_tracker WHERE (id>1200000) AND (id<1250000);
>>
>> I didn't populate any test tables, but I'd expect that to do just as
>> well without being any uglier than the original query is.
>
> You're absolutely right, but I'm afraid this won't help. I'd simplified
> the original example query, but in real life, I've got about 50
> different sub-ranges, which cover virtually all the id-space.
>

Well, it probably shouldn't use the index if it covers the vast majority of the table. I wonder if it is actually faster to reformulate with WHERE or not at that point -- it might be slower.