MySQL query need optimization

Question

Hi all,
I got this query:

SELECT user_id  
FROM basic_info  
WHERE age BETWEEN 18 AND 22 AND gender = 0  
ORDER BY rating  
LIMIT 50

The table looks like (and it contains about 700k rows):

CREATE TABLE IF NOT EXISTS `basic_info` (  
  `user_id` mediumint(8) unsigned NOT NULL auto_increment,  
  `gender` tinyint(1) unsigned NOT NULL default '0',  
  `age` tinyint(2) unsigned NOT NULL default '0',  
  `rating` smallint(5) unsigned NOT NULL default '0',  
  PRIMARY KEY  (`user_id`),  
  KEY `tmp` (`gender`,`rating`),  
) ENGINE=MyISAM;

The query itself is optimized but it has to walk about 200k rows to do his job. Here's the explain output:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1   SIMPLE  basic_info  ref  tmp,age  tmp  1  const  200451  Using where

Is it possible to optimize the query so it won't walk over 200k rows ?

Thanks !

Answer 1

Extend you tmp-key to include the age-column:

KEY `tmp` (`age`,`gender`,`rating`)

Answer 2

There are two useful indexes that can help this query:

KEY gender_age (gender, age) -- this index can satisfy both the gender=0 condition as well as age BETWEEN 18 AND 22. However, because you have a range condition over the age field, adding the rating column to the index will not give sorted results -- hence MySQL will select all matching rows -- ignoring your LIMIT clause -- and do an additional filesort regardless.

KEY gender_rating (gender, rating) -- the index you already have; this index can satisfy the gender=0 condition and retrieves data already sorted by rating. However, the database has to scan all elements with gender=0 and eliminate those who are not in range age BETWEEN 18 AND 22

Changing schema

If the above does not help you enough, changing your schema is always possible. One such approach is turning the age BETWEEN condition into an equality condition, by defining an age group column; for instance, ages 0-12 will be in age group 1, ages 12-18 in age group 2, etc.

This way, having an index with (gender, agegroup, rating) and query with WHERE gender=0 AND agegroup=3 ORDER BY rating will retrieve all results from the index and already sorted. In this case, the LIMIT clause should only fetch 50 entries from the table and no more.

Answer 3

Attempt to use InnoDB to improve performence?

Benchmarking here