Posted by Jim Michaels on 01/11/06 13:02

I would also get rid of the 1 AND out of the WHERE clause. the SQL compiler
would prefer a boolean expression like 1=1 rather than a numeric result.
But really you can just hand-optimize this out since it really does nothing
at all according to the truth tables for AND.
Essentially, if you hard-wire one side of an AND to TRUE, then whatever
comes in to AND on the other side just comes out as a result: a
do-nothing-special - a plain wire from input to output.

"Anze" <anzenews@volja.net> wrote in message
news:79iof.20712$h6.742430@news.siol.net...
>> Why do you use "COUNT( a0.count )"? If there's no NULL values in
>> "a0.count" column, then it's results are same as "COUNT( * )", which may
>> be faster. If there are NULL values, then you could add "a0.count IS NOT
>> NULL" to WHERE clause and use "COUNT( * )" - it may still be a bit
>> faster.
>> Or maybe you wanted to use "SUM( a0.count )" or "COUNT( DISTINCT a0.count
>> )"?
>
> Sorry, a typo... :)
> I simplified the original query so it would be easier to optimize. This is
> the original query:
>
> select a0.count
> 'count',ids.category,ids.textId,ids.created,ids.descr,ids.descr_title from
> srchIds ids, srchWords a0 where 1 and a0.word LIKE '%abc%' and
> a0.id=ids.id
> group by ids.category, ids.textId order by ids.created desc, 'count' desc,
> ids.textId desc
>
> Would LEFT JOIN speed up the queries? I have a feeling that it shouldn't
> matter...
>
>>> What is interesting is that it doesn't consider the key on field
>>> 'category'
>>
>> It's probably because it still has to view all the table (or rather table
>> join product). It does not need to view it category by category - it can
>> go record by record and add the viewed values of "a0.count" to the result
>> for the corresponding category (which may allready be in the result).
>
> Actually, it should only take the records from srchIds that have a
> corresponding entry in srchWords, where word is the one we are searching
> for... So it should use category index.
>
> I think I have it all figured out. I was a combination of "LIKE
> '%abc%" (which can't be indexed) and... a small table. MySQL desided it
> was
> more efficient not to use indexes because the table was so small. When I
> ran the query on a bigger table it used the indexes. Weird. :(
>
> Thank you for your help!
>
> Anze

[Back to original message]