Posted by Hilarion on 12/15/05 22:17

>> 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

You should use "`" instead of "'" for "count" column. "'" is for string
values, and "`" is for identifiers. Also you do not really need to specify
"count" as alias for "a0.count" because it's allready named "count" so
you are not changing anything here.

You also do not perform any aggregation, so GROUP BY here does nothing
(beside maybe slowing down the query execution). So it should be something
like this:

SELECT
a0.count,
ids.category,
ids.textId,
ids.created,
ids.descr,
ids.descr_title
FROM
srchIds AS ids INNER JOIN
srchWords AS a0 ON a0.id = ids.id
WHERE
a0.word LIKE '%abc%'
ORDER BY
ids.created DESC,
a0.count DESC,
ids.textId DESC


> Would LEFT JOIN speed up the queries? I have a feeling that it shouldn't
> matter...

Try INNER JOIN (looking at your query left outer join is probably not what
you need). In general you should use JOIN syntax instead of table list
and WHERE clause (for join purposes, not for filtering).


>>> 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...

It does take only the records from srchIds that have corresponding
entry in srchWords (your query and mine with INNER JOIN).


> So it should use category index.

But category index is not related to this - it's not involved in joining
the tables nor in filtering the results. Only thing it could be used
for is grouping (but it will not help in any way even if you use some
aggregation) or sorting (but you do not sort by category).


> I think I have it all figured out. I was a combination of "LIKE
> '%abc%" (which can't be indexed)

This only influences use of index on a0.word. Indexes on text fields
can be used only for comparison ("=", "<", "<=", ">", ">=", "BETWEEN",
"IS NULL", "IS NOT NULL") and sorting. They do not help if you check
text parts (which includes "LIKE" and matching substrings etc.).


> 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. :(

Which indexes?


> Thank you for your help!

No problem.



Hilarion

[Back to original message]