dk4SGZhe

1Proper index/query when using INNER JOIN
2SELECT logentry_id, date, log_codes.log_desc FROM log_entries
3INNER JOIN log_codes ON log_entries.log_code = log_codes.log_code 
4ORDER BY logentry_id DESC
5	
6WHERE partner_id = 1
7
8WHERE log_codes.category_overview = 1
9
10WHERE partner_id = 1 AND log_codes.category_overview = 1
11
12WHERE partner_id = 1 AND customer_id = 1 AND log_codes.category_overview = 1
13	
14CREATE TABLE IF NOT EXISTS `log_codes` (
15  `log_code` smallint(6) NOT NULL,
16  `log_desc` varchar(255),
17  `category_mail` tinyint(1) NOT NULL,
18  `category_overview` tinyint(1) NOT NULL,
19  `category_cron` tinyint(1) NOT NULL,
20  `category_documents` tinyint(1) NOT NULL,
21  `category_error` tinyint(1) NOT NULL,
22  PRIMARY KEY (`log_code`)
23) ENGINE=MyISAM DEFAULT CHARSET=utf8;
24
25CREATE TABLE IF NOT EXISTS `log_entries` (
26  `logentry_id` int(11) NOT NULL AUTO_INCREMENT,
27  `date` datetime NOT NULL,
28  `log_code` smallint(6) NOT NULL,
29  `partner_id` int(11) NOT NULL,
30  `customer_id` int(11) NOT NULL,
31  PRIMARY KEY (`logentry_id`)
32) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
33	
34EXPLAIN SELECT log_entries.logentry_id, log_entries.date, log_codes.log_code_desc FROM log_entries
35INNER JOIN log_codes ON log_entries.log_code = log_codes.log_code
36WHERE log_entries.partner_id = 1 AND log_codes.category_overview = 1 ORDER BY logentry_id DESC
37	
38EXPLAIN SELECT log_entries.logentry_id, log_entries.date, log_codes.log_code_desc FROM log_entries 
39INNER JOIN log_codes ON log_entries.log_code = log_codes.log_code 
40LEFT JOIN partners ON log_entries.partner_id = partners.partner_id 
41LEFT JOIN joined_table1 ON log_entries.t1_id = joined_table1.t1_id 
42LEFT JOIN joined_table2 ON log_entries.t2_id = joined_table2.t2_id 
43LEFT JOIN joined_table3 ON log_entries.t3_id = joined_table3.t3_id 
44LEFT JOIN joined_table4 ON joined_table3.t4_id = joined_table4.t4_id 
45LEFT JOIN joined_table5 ON log_entries.t5_id = joined_table5.t5_id 
46LEFT JOIN joined_table6 ON log_entries.t6_id = joined_table6.t6_id
47WHERE log_entries.partner_id = 1 AND log_codes.category_overview = 1 ORDER BY logentry_id DESC;
48	
49EXPLAIN SELECT log_entries.logentry_id, log_entries.date, log_codes.log_code_desc FROM log_entries 
50INNER JOIN log_codes ON log_entries.log_code = log_codes.log_code 
51LEFT JOIN partners ON log_entries.partner_id = partners.partner_id 
52LEFT JOIN joined_table1 ON log_entries.t1_id = joined_table1.t1_id 
53LEFT JOIN joined_table2 ON log_entries.t2_id = joined_table2.t2_id 
54LEFT JOIN joined_table3 ON log_entries.t3_id = joined_table3.t3_id 
55LEFT JOIN joined_table4 ON joined_table3.t4_id = joined_table4.t4_id 
56LEFT JOIN joined_table5 ON log_entries.t5_id = joined_table5.t5_id 
57LEFT JOIN joined_table6 ON log_entries.t6_id = joined_table6.t6_id
58WHERE log_entries.partner_id = 1 AND log_codes.category_overview = 1 ORDER BY logentry_id DESC;
59	
60EXPLAIN SELECT log_entries.logentry_id, log_entries.date, log_codes.log_code_desc FROM log_entries INNER JOIN log_codes ON log_entries.log_code = log_codes.log_code
61WHERE log_entries.partner_id = 1
62AND log_entries.log_code IN (SELECT log_code FROM log_codes WHERE category_overview = 1) ORDER BY logentry_id DESC;
63	
64EXPLAIN SELECT log_entries.logentry_id, log_entries.date, log_codes.log_code_desc FROM log_entries INNER JOIN log_codes ON log_entries.log_code = log_codes.log_code
65WHERE log_entries.partner_id = 1
66AND log_entries.log_code IN (SELECT log_code FROM log_codes WHERE category_overview = 1) ORDER BY logentry_id DESC;
67	
68EXPLAIN SELECT log_entries.logentry_id, log_entries.date, log_codes.log_code_desc FROM log_entries INNER JOIN log_codes ON log_entries.log_code = log_codes.log_code
69WHERE log_entries.partner_id = 1 AND log_entries.customer_id = 10000
70AND log_entries.log_code IN (SELECT log_code FROM log_codes WHERE category_overview = 1) ORDER BY logentry_id DESC;
71	
72EXPLAIN SELECT log_entries.logentry_id, log_entries.date, log_codes.log_code_desc FROM log_entries INNER JOIN log_codes ON log_entries.log_code = log_codes.log_code
73WHERE log_entries.partner_id = 1 AND log_entries.customer_id = 10000
74AND log_entries.log_code IN (SELECT log_code FROM log_codes WHERE category_overview = 1) ORDER BY logentry_id DESC;
75	
76ALTER TABLE `log_codes`
77    ADD INDEX `IX_overview_code` (`category_overview`, `log_code`);
78
79ALTER TABLE `log_entries`
80    ADD INDEX `IX_partner_code` (`partner_id`, `log_code`),
81    ADD INDEX `IX_customer_partner_code` (`customer_id`, `partner_id`, `log_code`);
82	
83ALTER TABLE `log_codes`
84    ADD INDEX `IX_overview_code` (`category_overview`, `log_code`);
85
86ALTER TABLE `log_entries`
87    ADD INDEX `IX_partner_code` (`partner_id`, `log_code`),
88    ADD INDEX `IX_customer_partner_code` (`customer_id`, `partner_id`, `log_code`);