Optimising MYSQL queries

Question

I have a legacy PHP application (uses no framework) - in places it has some real slowdown with some queries taking 8-10 seconds.

Below is the extract from one of these slow queries I can see that I am getting filesort which indicates that this is running slow for the reason (or at least I presume) - can anyone suggest how to optimize my queries to prevent using filesort? The table has around 600,000 rows (so its fairly large)

Schema added:
(MailList_Tags)
Field   Type    Null    Key Default Extra
MailListID  int(11)     PRI 0    
Tag varchar(60)     PRI      

(MailList)    
Field   Type    Null    Key Default Extra
MailListID  int(11)     PRI NULL    auto_increment
GroupID varchar(8)  YES     NULL     
HotelID varchar(8)  YES     NULL     
Title   varchar(20) YES     NULL     
FirstName   blob    YES     NULL     
LastName    blob    YES     NULL     
CompanyName varchar(200)    YES     NULL     
Address blob    YES     NULL     
Postcode    varchar(12) YES     NULL     
Country varchar(200)    YES     NULL     
Tel varchar(40) YES     NULL     
Fax varchar(40) YES     NULL     
Email   blob                 
md5digest   varchar(32)     UNI      
Sub1    int(1)      MUL 0    
Sub2    int(1)      MUL 0    
Sub3    int(1)      MUL 0    
OptInState  char(1)     MUL P    
UpdateDetailsState  char(1) YES     NULL     
Bounce  int(11)         0


EXPLAIN SELECT  `Tag` , COUNT( DISTINCT (
`MailList_Tags`.`MailListID`
) ) AS  `Count` 
FROM  `MailList` 
JOIN  `MailList_Tags` ON  `MailList`.`MailListID` =  `MailList_Tags`.`MailListID` 
WHERE HotelID =  'ca4b9ac9'
AND OptInState =  'V'
GROUP BY  `Tag`

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  MailList_Tags   index   MailListID  MailListID  64  NULL    962583  Using index; Using filesort
1   SIMPLE  MailList    eq_ref  PRIMARY,OptInState  PRIMARY 4   user_db.MailList_Tags.MailListID    1   Using where

could you show the schema of `MailList` and `MailList_Tags` table? — Shiplu Mokaddim, May 04 '12 at 10:59
600K is rather small. You are probably missing some indexes. — ypercubeᵀᴹ, May 04 '12 at 11:02
@ypercube - is there a way to generate that info via phpmyadmin? — Zabs, May 04 '12 at 11:21
[How to ask this kind of question](http://stackoverflow.com/questions/1204402/how-do-i-ask-for-help-optimizing-fixing-queries-in-mysql) — Matt Fenwick, May 04 '12 at 12:51

ypercubeᵀᴹ · Accepted Answer · 2012-05-04T11:41:14.170

1

You need some indexes:

on table MailList_Tags, add a UNIQUE index on (Tag, MailListID)
on table MailList, add an index on (OptInState, HotelID, MailListID).

Then try this query (changed COUNT( DISTINCT MailList_Tags.MailListID ) to COUNT(*), should produce the same result):

SELECT  Tag 
     ,  COUNT( * )
          AS  `Count` 
FROM  MailList 
  JOIN  MailList_Tags 
    ON  MailList.MailListID = MailList_Tags.MailListID 
WHERE  HotelID =  'ca4b9ac9'
  AND  OptInState =  'V'
GROUP BY  Tag

or this one:

SELECT  Tag 
     ,  COUNT(*)
          AS  `Count` 
FROM  MailList 
WHERE  EXISTS 
       ( SELECT  *
         FROM  MailList_Tags 
         WHERE  MailList.MailListID = MailList_Tags.MailListID 
           AND  HotelID =  'ca4b9ac9'
           AND  OptInState =  'V'
       )
GROUP BY  Tag

edited May 04 '12 at 11:41

answered May 04 '12 at 11:30

ypercubeᵀᴹ

113,259
19
174
235

Isn't `(MailListId, Tag)` the primary key on `MailList_Tags? (rendering the proposed unique index unnecessary) – Matt Fenwick May 04 '12 at 11:32
1

You can make it a simple index, not Unique. But the order in the index is needed. I always have 2 indexes in all similar many-to-many tables `(a,b)` and `(b,a)`. – ypercubeᵀᴹ May 04 '12 at 11:36
@Matt: The Primary Key is probably the reverse of what is needed for this query. – ypercubeᵀᴹ May 04 '12 at 11:50
Thanks - I will have a play and see if i can optimise the speeds abit :) cheers guys – Zabs May 04 '12 at 12:07

Optimising MYSQL queries

1 Answers1