Why my mysql doesn't use index?

Question

I'm tuning my query for mysql. the schema has index of user_id (following..) but the index is not used. why?

Env: MySQL4.0.27,MyISAM

SQL is the following :

SELECT type,SUM(value_a) A, SUM(value_b) B, SUM(value_c) C
FROM  big_record_table
WHERE  user_id='<user_id>'
GROUP BY type

Explain:

|table |type |possible_keys |key |key_len |ref |rows |Extra|

|big_record_table| ALL| user_id_key|||| 1059756 |Using where; Using temporary; Using filesort|

could you describe detail?

scheme is following:

CREATE TABLE `big_record_table` (
 `user_id` int(11) NOT NULL default '0',
 `type` enum('type_a','type_b','type_c') NOT NULL default 'type_a',
 `value_a` bigint(20) NOT NULL default '0',
 `value_b` bigint(20) default NULL,
 `value_c` bigint(20) NOT NULL default '0',
 KEY `user_id_key` (`user_id`)
) TYPE=MyISAM

Answer 1

First, we don't see how your indexes are declared. Can you get a dump of the tables? In PostgreSQL you'd use pg_dump but I don't know how in MySQL. Have you done an ANALYZE on the table?

Answer 2

My guess is that type and user_id are not indexed.

Just a will run. You're not giving much to play with.

Answer 3

It could be that implicit type conversion is preventing your index from being used. You have defined user_id as an int, but specified a string in the query. This gives MySQL the option of either converting the string in the query into an int (which might not be accurate) - or convert every user_id in the database into a string to compare against the string in the query.

Short answer: try removing the quotes in the query

SELECT type,SUM(value_a) A, SUM(value_b) B, SUM(value_c) C
FROM  big_record_table
WHERE  user_id=123
GROUP BY type

(where 123 is replaced with the correct user-id).