sardine sardine - 4 months ago 9
SQL Question

A correct MySQL Left Join Query overburdens a smallsized DB although rows are indexed

I have this working SQL query but it almost makes my DB crash:

SELECT MASTER.master_id,
MASTER.master_summary,
MASTER.master_start,
MASTER.master_end,
MASTER.master_risk,
MASTER.master_source,
MASTER.master_veto,
master.master_tags,
NULL AS HAS_CE,
C2C.c2c_customer
FROM `cer_master` MASTER
LEFT JOIN `cer_c2customer` C2C
ON ( C2C.c2c_id = MASTER.master_id AND C2C.c2c_source = MASTER.master_source )

WHERE ( MASTER.master_id NOT LIKE 'TAV%' )
AND (( MASTER.master_class <> 'type2' ) OR ( MASTER.master_class <> 'type3' ))
AND ( MASTER.master_status <> 'Cancelled' )
AND ( MASTER.master_end >= Now() AND MASTER.master_start >= Date_sub(Now(), INTERVAL 1 day) )


If I try to run this on phpMyAdmin I have to literally wait for 5min and get this result: 3,699 total, Query took 0.9358 sec

I have indexed
MASTER.master_id, MASTER.master_start, MASTER.master_end, MASTER.master_source
aswell as
c2c.c2c_id, C2C.c2c_source and C2C.c2c_customer
but it doesn't seem to help.

Additional Info:
cer_master MASTER
table has 277,502 rows and
cer_c2customer C2C
table has 72,788 rows.

Can someone help me optimize this query? I need it badly and cannot think of another way.

EDIT: Results from the EXPLAIN query:

+----+-------------+--------+-------+-------------------------------------------------------+---------------------------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+-------------------------------------------------------+---------------------------------+---------+------+-------+-------------+
| 1 | SIMPLE | MASTER | range | CHM_MASTER_SCHEDULED_START_DATE,CHM_MASTER_SCHEDUL... | CHM_MASTER_SCHEDULED_START_DATE | 4 | NULL | 5042 | Using where |
+----+-------------+--------+-------+-------------------------------------------------------+---------------------------------+---------+------+-------+-------------+
| 1 | SIMPLE | C2C | ALL | CER_C2C_CHANGE_ID | NULL | NULL | NULL | 72788 | |
+----+-------------+--------+-------+-------------------------------------------------------+---------------------------------+---------+------+-------+-------------+

Table Create Table
master CREATE TABLE `master` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
` MASTER_LAST_MODIFIED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
` MASTER_SOURCE` varchar(16) NOT NULL,
` MASTER_ID` varchar(16) NOT NULL,
` MASTER_SUMMARY` text NOT NULL,
` MASTER_NOTES` text NOT NULL,
` MASTER_SERVICE` varchar(255) NOT NULL,
` MASTER_SITE` text NOT NULL,
` MASTER_START` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
` MASTER_END` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
` MASTER_DEPARTMENT_FLAG` varchar(8) NOT NULL,
` MASTER_RISK` int(8) NOT NULL DEFAULT '1',
` MASTER_IMPACT_LEVEL` varchar(64) NOT NULL,
` MASTER_TOOL_STATUS` varchar(32) NOT NULL,
` MASTER_IMPACT_RISK_NOTES` text NOT NULL,
` MASTER_CALENDAR_WEEK` varchar(16) NOT NULL,
` MASTER_TAGS` varchar(1024) NOT NULL,
` MASTER_VETO` tinyint(1) NOT NULL DEFAULT '0',
` MASTER_LAYER_TAGS` text NOT NULL,
` MASTER_ORAKEL_ID` int(11) NOT NULL DEFAULT '0',
` MASTER_USED_TEMPLATE` text NOT NULL,
PRIMARY KEY (`ID`),
KEY ` MASTER_ID` (` MASTER_CHANGE_ID`),
KEY ` MASTER_LAST_MODIFIED_DATE` (` MASTER_LAST_MODIFIED_DATE`),
KEY ` MASTER_SERVICE` (` MASTER_SERVICE`),
KEY ` MASTER_START` (` MASTER_START`),
KEY ` MASTER_END` (` MASTER_END_`),
KEY ` MASTER_SOURCE` (` MASTER_SOURCE`)
) ENGINE=MyISAM AUTO_INCREMENT=278315 DEFAULT CHARSET=utf8


and this is show create table from c2c_customer:
cerberus_change2customer

CREATE TABLE `c2c_customer` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`C2C_SOURCE` text NOT NULL,
`C2C_ID` text NOT NULL,
`C2C_CUSTOMER` text NOT NULL,
`C2C_LAST_MODFIED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
FULLTEXT KEY `C2C_ID` (`C2C_ID`),
FULLTEXT KEY `C2C_CUSTOMER` (`C2C_CUSTOMER`)
) ENGINE=MyISAM AUTO_INCREMENT=516044 DEFAULT CHARSET=utf8

Ben Ben
Answer

Your index should be on all columns, not separate indexes for each column.

For example:

ALTER TABLE `cer_c2customer` ADD INDEX `cer_c2customer_ID_SOURCE_CUSTOMER` (c2c_id, c2c_source, c2c_customer)

This means that the one index can be used to locate the data and also supply all of the columns required from this table in the query.

In addition you probably want the clustering index on cer_master to be the start date or end date.