The following query is run on user_chars (approx 20mm records) and user_data (approx 10mm records). The query runs too slowly and I was wondering if better composite indexes might improve the situation.
Any idea on what the best composite index would be?
SELECT username, title, status
FROM (
SELECT username, title, status
FROM user_chars w, user_data r
WHERE w.user_id = r.user_id
AND (status < '300' OR is_admin = '1')
AND (
(rating_id = 'rating1' AND rating BETWEEN 55 AND 65)
OR (rating_id = 'rating2' AND rating BETWEEN 50 AND 60)
OR (rating_id = 'rating3' AND rating BETWEEN 30 AND 40)
OR (rating_id = 'rating4' AND rating BETWEEN 90 AND 100)
...
)
GROUP BY w.user_id
HAVING COUNT(*) >= 3
) data
WHERE username != '0'
AND title != '0'
And here following are the tables:
CREATE TABLE user_data (
user_id int(10) unsigned NOT NULL AUTO_INCREMENT,
username decimal(17,14) DEFAULT NULL,
title decimal(17,14) DEFAULT NULL,
status smallint(6) unsigned NOT NULL,
is_admin tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (user_id),
KEY username (username),
KEY title (title),
KEY status (status),
KEY is_admin (is_admin),
KEY chars_avg_index (user_id,username,title,status),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE user_chars (
user_id int(10) unsigned NOT NULL,
rating_id char(32) DEFAULT NULL,
rating tinyint(3) unsigned NOT NULL,
PRIMARY KEY (user_id),
KEY rating_id (rating_id),
KEY rating (rating),
KEY chars_index (user_id,rating_id,rating)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
EDIT: Added the EXPLAIN
+----+-------------+------------+--------+--------------------------------------------+-----------------+---------+-----------+-------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+--------------------------------------------+-----------------+---------+-----------+-------+-----------------------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3668 | Using where | | 2 | DERIVED | w | range | user_id,rating_id,rating,chars_index | chars_index | 98 | NULL | 13215 | Using where; Using index; Using temporary; Using filesort | | 2 | DERIVED | r | eq_ref | PRIMARY,status,is_admin,chars_avg_index | PRIMARY | 4 | w.user_id | 1 | Using where | +----+-------------+------------+--------+--------------------------------------------+-----------------+---------+-----------+-------+-----------------------------------------------------------+