|
|
Posted by Hilarion on 12/15/05 18:17
> I stumbled across this query that I just can't optimize - even if I create
> all possible indexes it doesn't use them. Could someone please clue me in?
> I have no idea why MySQL doesn't use the indexes. :(
>
> The query in question is:
>
> mysql> explain select count(a0.count) from srchIds ids, srchWords a0 where
> a0.word LIKE '%abc%' and a0.id=ids.id group by ids.category;
> [...]
I'd rephrase the query like this (changed it to use INNER JOIN and also
included "ids.category" in the result):
SELECT
ids.category,
COUNT( a0.count ) AS srchWords_count
FROM
srchIds AS ids INNER JOIN
srchWords AS a0 ON a0.id = ids.id
WHERE
a0.word LIKE '%abc%'
GROUP BY
ids.category
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 )"?
Only indexes that I see useful for this query are on "a0.id", "ids.id"
and "ids.category" (see below).
> 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).
Hilarion
Navigation:
[Reply to this message]
|