MySQL Optimization - Joining Large Tables

Question

To start here, this is a simplified version of the tables used.

tbl_map has approximately 4,000,000 lines, tbl_1 has approximately 120 lines, tbl_2 contains approximately 5,000,000 lines. I know that data should not be considered large, given that Google, Yahoo !, etc. Use much larger datasets. So I just assume that something is missing.

  CREATE TABLE `tbl_map` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `tbl_1_id` bigint(20) DEFAULT '-1', `tbl_2_id` bigint(20) DEFAULT '-1', `rating` decimal(3,3) DEFAULT NULL, PRIMARY KEY (`id`), KEY `tbl_1_id` (`tbl_1_id`), KEY `tbl_2_id` (`tbl_2_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `tbl_1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `tbl_2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `data` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Query of interest: also instead of ORDER BY RAND() , ORDERY BY t.id DESC . The request takes as much as 5-10 seconds and causes a significant expectation when users view this page.

 EXPLAIN SELECT t.data, t.id , tm.rating FROM tbl_2 AS t JOIN tbl_map AS tm ON t.id = tm.tbl_2_id WHERE tm.tbl_1_id =94 AND tm.rating IS NOT NULL ORDER BY t.id DESC LIMIT 200 1 SIMPLE tm ref tbl_1_id, tbl_2_id tbl_1_id 9 const 703438 Using where; Using temporary; Using filesort 1 SIMPLE t eq_ref PRIMARY PRIMARY 8 tm.tbl_2_id 1 

I just wanted to speed up the query, make sure I have the appropriate indexes, etc. I appreciate any advice from DB Gurus! Thank you

Answer 1

PROPOSAL: Index the table as follows:

 ALTER TABLE tbl_map ADD INDEX (tbl_1_id,rating,tbl_2_id); 

Answer 2

As with Rolando, you definitely need an index in the map table, but I would expand it to TABLE, including tbl_2_id, which for your ORDER BY clause is the identifier of table 2 (which is in the same table as the map, so just use this index.In addition, since the index now contains all 3 fields and is based on the key search identifier and criteria for zero (or not) rating, the third element already has them for your ORDER BY clause.

INDEX (tbl_1_id, rating, tbl_2_id);

Then I would just ask how

 SELECT STRAIGHT_JOIN t.data, t.id , tm.rating FROM tbl_map tm join tbl_2 t on tm.tbl_2_id = t.id WHERE tm.tbl_1_id = 94 AND tm.rating IS NOT NULL ORDER BY tm.tbl_2_id DESC LIMIT 200