|
|
Posted by Anze on 12/15/05 19:58
> 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
Navigation:
[Reply to this message]
|