|
Posted by Anze on 12/15/05 16:21
Hi all!
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;
+----+-------------+-------+------+---------------+------+---------+----------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+------+---------+----------------+------+---------------------------------+
| 1 | SIMPLE | ids | ALL | PRIMARY | NULL | NULL | NULL
| 330 | Using temporary; Using filesort |
| 1 | SIMPLE | a0 | ref | id | id | 5 |
monitor.ids.id | 144 | Using where |
+----+-------------+-------+------+---------------+------+---------+----------------+------+---------------------------------+
2 rows in set (0.00 sec)
What is interesting is that it doesn't consider the key on field 'category':
mysql> show keys from srchIds;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| srchIds | 0 | PRIMARY | 1 | id | A
| 330 | NULL | NULL | | BTREE | |
| srchIds | 1 | category | 1 | category | A
| 7 | NULL | NULL | YES | BTREE | |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)
Any help would be appreciated - I'm lost here.
Thank you!
Anze
Navigation:
[Reply to this message]
|