MySQL Select Optimization Help?_问答_开发者_运维开发者技术经验分享

This is a broad example of a SELECT query I use frequently on one of my sites. We are having very bad issues with slow load page times with our host, so I am trying to do everything I can to optimize every bit of code the site uses. I am no expert when it comes to MySQL, so I am hoping some of you may be of assistance. Here is the query I am trying to optimize a bit more -

Select ID, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15 From table_1
Where Active = '1' And Col2 LIKE '%Cat%' And Col3 <> 'blah' And Col3 <> 'blah1'  And Col3 <> 'blah2' And Col3 <> 'blah3' And Col3 <> 'blah4' And Col3 <> 'blah5' And Col3 <> 'blah6'
And ID Not In (Select t2ID From table_2 Where table_2.t2ID = table_1.ID And table_2.Col1 = '1' And table_2.Col2 = '1')
And ID Not In (Select t3ID From table_3 Where table_3.t3ID = table_1.ID And table_3.Col1 = '1')
And ID Not In (Select t4ID From table_4 Where table_4.t4ID = table_1.ID And table_4.Col1 = '1')

Basically it checks 1 table (table_1) and pulls all the rows that match that are not found in table_2, table_3, and table_4. I am sure there is a much more efficient way to do this other than multiple sub selects. Any help is greatly appreciated! Thanks in advance :)

UPDATE: Basically all I am curious to find out is, is there something quicker than the multiple sub selects in the query? I am sure that there is some way to get the results from one table that do not exist in multiple other tables that is a lot more efficient than doing sub selects... The only commonality between the tables is that the ID from table_1, is identical to another column in each of the other 3 tables (which is what I am currently checking it against now using the sub selects). Unfortunately, I just cant figure out what the more efficient way to do this query is... Thanks for everyone's input thus far!

TABLE LAYOUT

mysql> show create table campaigns\G  
*************************** 1. row ***************************  
Table: campaigns  
Create Table: CREATE TABLE `campaigns` (  
`ID` int(11) NOT NULL auto_increment,  
`CreatedOn` datetime NOT NULL,  
`AddedBy` varchar(75) default NULL,  
`pCampaignName` varchar(255) default NULL,  
`CampaignName` varchar(255) default NULL,  
`CampaignValue` decimal(65,2) default '0.00',  
`CampaignPayout` decimal(65,2) NOT NULL default '0.00',  
`CampaignT` double NOT NULL default '0',  
`CampaignSD` double NOT NULL default '0',  
`ReportingTime` varchar(255) default NULL,  
`CampaignExpiration` varchar(100) default NULL,  
`DurationType` varchar(100) default NULL,  
`Countries` varchar(100) default NULL,  
`CampaignDescription` longtext,  
`CampaignRequirements` longtext,  
`CampaignType` varchar(50) default NULL,  
`CampaignID` varchar(255) default NULL,  
`BannerImageWidth` int(10) NOT NULL default '0',  
`BannerImageHeight` int(10) NOT NULL default '0',  
`BannerImageURL` varchar(255) default NULL,  
`BannerImageAlternateText` varchar(255) default NULL,  
`DisplayBanner` int(1) NOT NULL default '0',  
`CampaignCode` longtext,  
`CampaignURL` longtext,  
`CampaignActive` int(1) NOT NULL default '0',  
`Status` varchar(255) default NULL,  
`Affiliate` varchar(255) default NULL,  
`NewOfferEmailSent` int(1) unsigned NOT NULL default '0',  
`NumberApproved` double(65,2) NOT NULL default '0.00',  
`NumberLeads` double NOT NULL default '0',  
`ThumbsUp` double NOT NULL default '0',  
`ThumbsDown` double NOT NULL default '0',  
`CampaignPoints` double NOT NULL default '0',  
`UserRatingUp` double NOT NULL default '0',  
`UserRatingDown` double NOT NULL default '0',  
PRIMARY KEY  (`ID`)  
) ENGINE=MyISAM AUTO_INCREMENT=1608 DEFAULT CHARSET=utf8  
1 row in set (0.09 sec)  

mysql> show indexes from campaigns\G  
*************************** 1. row ***************************  
Table: campaigns  
Non_unique: 0  
Key_name: PRIMARY  
Seq_in_index: 1  
Column_name: ID  
Collation: A  
Cardinality: 1596  
Sub_part: NULL  
Packed: NULL  
Null:  
Index_type: BTREE  
Comment:  
1 row in set (0.10 sec)  

mysql>  
mysql> show create table acampaigns\G  
*************************** 1. row ***************************  
Table: acampaigns  
Create Table: CREATE TABLE `acampaigns` (  
`ID` int(11) NOT NULL auto_increment,  
`CreatedOn` datetime NOT NULL,  
`CampaignName` varchar(255) default NULL,  
`CampaignRequirements` longtext,  
`CampaignURL` longtext,  
`CampaignValue` decimal(65,2) NOT NULL,  
`CampaignPayout` decimal(65,2) NOT NULL,  
`C开发者_开发知识库ampaignReferralCommissionTier1` decimal(65,2) NOT NULL default '0.20',  
`CampaignReferralCommissionTier2` decimal(65,2) NOT NULL default '0.10',  
`CampaignT` double NOT NULL default '0',  
`CampaignSD` double NOT NULL default '0',  
`CampaignType` varchar(255) default NULL,  
`CampaignID` varchar(100) default NULL,  
`CampaignExpiration` varchar(100) default NULL,  
`CampaignReturnStatus` varchar(100) default NULL,  
`CampaignStatus` varchar(255) default NULL,  
`pCampaignID` int(11) NOT NULL,  
`pCampaignName` varchar(255) default NULL,  
`pUserID` int(11) NOT NULL,  
`pUsername` varchar(75) default NULL,  
`pUserIPAddress` varchar(30) default NULL,  
`ApprovedOn` datetime NOT NULL,  
`MarkedDone` int(1) NOT NULL default '0',  
`Notes` longtext,  
`PaidOn` datetime default NULL,  
`cBonus` decimal(65,2) NOT NULL default '0.00',  
`ReversedReason` varchar(255) default NULL,  
`CampaignPoints` double NOT NULL default '0',  
`Affiliate` varchar(255) default NULL,  
`RC1Paid` int(1) unsigned NOT NULL default '0',  
`RC2Paid` int(1) unsigned NOT NULL default '0',  
PRIMARY KEY  (`ID`)  
) ENGINE=MyISAM AUTO_INCREMENT=10996 DEFAULT CHARSET=utf8  
1 row in set (0.44 sec)  

mysql> show indexes from acampaigns\G  
*************************** 1. row ***************************  
Table: acampaigns  
Non_unique: 0  
Key_name: PRIMARY  
Seq_in_index: 1  
Column_name: ID  
Collation: A  
Cardinality: 8936  
Sub_part: NULL  
Packed: NULL  
Null:  
Index_type: BTREE  
Comment:  
1 row in set (0.09 sec)  

mysql>  
mysql> show create table bcampaigns\G  
*************************** 1. row ***************************  
Table: bcampaigns  
Create Table: CREATE TABLE `bcampaigns` (  
`ID` int(11) NOT NULL auto_increment,  
`CreatedOn` datetime NOT NULL,  
`pCampaignID` int(11) NOT NULL,  
`ReportedByUserID` int(11) NOT NULL,  
`Status` varchar(255) default NULL,  
`Notes` longtext,  
PRIMARY KEY  (`ID`)  
) ENGINE=MyISAM AUTO_INCREMENT=375 DEFAULT CHARSET=utf8  
1 row in set (0.08 sec)  

mysql> show indexes from bcampaigns\G  
*************************** 1. row ***************************  
Table: bcampaigns  
Non_unique: 0  
Key_name: PRIMARY  
Seq_in_index: 1  
Column_name: ID  
Collation: A  
Cardinality: 0  
Sub_part: NULL  
Packed: NULL  
Null:  
Index_type: BTREE  
Comment:  
1 row in set (0.08 sec)  

mysql>  
mysql> show create table icampaigns\G  
*************************** 1. row ***************************  
Table: icampaigns  
Create Table: CREATE TABLE `icampaigns` (  
`ID` int(11) NOT NULL auto_increment,  
`CreatedOn` datetime NOT NULL,  
`pCampaignID` int(11) default NULL,  
`IgnoredByUserID` int(11) default NULL,  
PRIMARY KEY  (`ID`)  
) ENGINE=MyISAM AUTO_INCREMENT=567 DEFAULT CHARSET=utf8  
1 row in set (0.09 sec)  

mysql> show indexes from icampaigns\G  
*************************** 1. row ***************************  
Table: icampaigns  
Non_unique: 0  
Key_name: PRIMARY  
Seq_in_index: 1  
Column_name: ID  
Collation: A  
Cardinality: 532  
Sub_part: NULL  
Packed: NULL  
Null:  
Index_type: BTREE  
Comment:  
1 row in set (0.40 sec)  

mysql>
mysql> explain Select ID, CreatedOn, pCampaignName, 
CampaignName, CampaignRequirements, CampaignURL, Countries, 
CampaignPayout, CampaignPoints, CampaignT, CampaignSD, CampaignType, 
ReportingTime, NumberApproved, NumberLeads
From campaigns
-> Where CampaignActive = '1' And CampaignType LIKE 'Cat%' 
And CampaignType <> 'DS' And CampaignType <> 'CC'  And CampaignType <> 'PC' 
And CampaignType <> 'PC2' And CampaignType <> 'GCC' And CampaignType <> 'G' 
And CampaignType <> 'R'
-> And ID Not In (Select pCampaignID From acampaigns  
Where campaigns.ID = acampaigns.pCampaignID And MarkedDone = '1' And campaigns.pUserID = '1')
-> And ID Not In (Select pCampaignID From bcampaigns  
Where bcampaigns.pCampaignID = campaigns.ID And  bcampaigns.ReportedByUserID = '1')
-> And ID Not In (Select pCampaignID From icampaigns  
Where icampaigns.pCampaignID = campaigns.ID And icampaigns.IgnoredByUserID = '1')

Have you tried to EXPLAIN your query. If you aren't familiar with EXPLAIN, it will produce a report showing how the query is performed and especially which indexes are used, and may show up where an index is desirable but not present.

I have also found in the past that NOT IN can sometimes be horrendously slow. Perhaps rewriting NOT IN as NOT EXISTS may speed things up.

NOT EXISTS (SELECT *
                FROM table_3
                WHERE table_3.t3ID = table_1.ID AND
                      table_3.Col1 = '1')

IDFMA

please post the results of the following script so we can analyse your query in detail. if you could also pastebin the results in say http://pastie.org/ that would be great too.

thanks

show create table table_1\G
show indexes from table_1\G

show create table table_2\G
show indexes from table_2\G

show create table table_3\G
show indexes from table_3\G

show create table table_4\G
show indexes from table_4\G

explain
Select 
 ID, Col1, Col2, Col3, Col4, Col5, Col6, Col7, 
 Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15 
From 
 table_1
Where 
 Active = '1' And 
 Col2 LIKE '%Cat%' And Col3 <> 'blah' And Col3 <> 'blah1'  And Col3 <> 'blah2' And Col3 <> 'blah3' And 
 Col3 <> 'blah4' And Col3 <> 'blah5' And Col3 <> 'blah6' And 
 ID Not In (Select t2ID From table_2 Where table_2.t2ID = table_1.ID And table_2.Col1 = '1' And table_2.Col2 = '1') And 
 ID Not In (Select t3ID From table_3 Where table_3.t3ID = table_1.ID And table_3.Col1 = '1') And 
 ID Not In (Select t4ID From table_4 Where table_4.t4ID = table_1.ID And table_4.Col1 = '1');

You can split this single query into multiple separate steps and put them in a single stored procedure using temporary tables. This will make it more readable and probably run faster...