MySql , JOIN and Group By query is Using temporary and Filesort

Question

I have 2 tables as below:

CREATE TABLE ox_campaigns (
campaignid mediumint(9) NOT NULL auto_increment,
campaignname varchar(255) NOT NULL default '',
clientid mediumint(9) NOT NULL default '0',
is_deleted tinyint(1) NOT NULL default '0',
PRIMARY KEY (campaignid),
KEY ox_campaigns_clientid (clientid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE ox_clients (
clientid mediumint(9) NOT NULL auto_increment,
agencyid mediumint(9) NOT NULL default '0',
clientname varchar(255) NOT NULL default '',
is_deleted tinyint(4) NOT NULL,
PRIMARY KEY (clientid),
UNIQUE KEY ox_clients_account_id (account_id),
KEY ox_clients_agencyid (agencyid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

One client can have many campaigns linked to it.

I have a list of campaignids with me , what I want is a list of distinct clientids for those campaigns.

The query that I am using is:

SELECT clients.* 
    FROM 
        clients clients  
    JOIN 
        campaigns campaigns  ON clients.clientid = campaigns.clientid
    WHERE 
        campaigns.is_deleted=0 
        AND campaignid in (2325,2395)
        AND clients.is_deleted=0 
    GROUP BY clients.clientid

The EXPLAIN output that it gives is :

    +----+-------------+-----------+--------+-------------------------------+------+--------------------------------------------------------------------------------------------------------------------
    | id | select_type | table     | type  | possible_keys                 | key    | key_len| ref                      | rows| Extra                                     |
    +----+-------------+-----------+--------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------

    | 1  |    SIMPLE   | campaigns | range | PRIMARY,ox_campaigns_clientid | PRIMARY | 3     |             NULL         | 2 | Using where; Using temporary; Using filesort |

    | 1  |    SIMPLE   | clients   | eq_ref| PRIMARY                       | PRIMARY | 3     | openx.campaigns.clientid | 1 | Using where

Why it is using temporary and filesorting for this query ?

@Strawberry , actually one client can have multiple campaigns linked to it , and I want only distinct client information for the corresponding campaign ids — deGee
Use DISTINCT for that. Under certain circumstances, GROUP BY will produce unexpected results. Although, MySQL forgives the activity, GROUP BY should not be used in the absence of any aggregating functions. Note that this is not however a performance issue. — Strawberry

Gordon Linoff Gordon Linoff · Accepted Answer · 2014-01-16T13:15:21

It is using filesort because of the group by. You can prevent this by using an exists clause for what you are doing:

SELECT clients.* 
FROM clients clients  
WHERE exists (select 1
              from campaigns
              where clients.clientid = campaigns.clientid and
                    campaigns.is_deleted = 0 and
                    campaignid in (2325,2395)
             ) and
      clients.is_deleted = 0 ;

You have an index on campaigns_clientid(clientid), so this should use the index. A better index would be campaigns_clientid(clientid, is_deleted, campaign_id). This index "covers" the subquery. In other words, the engine will only use the index and not have to read in the data pages for that table.

MySql , JOIN and Group By query is Using temporary and Filesort

1 Answers