LEFT JOIN SELECT taking ages to run


We have a system were a CPE (customer premises equipment) “phones home” every 3 hours and a record is recorded in the cpelog. If there is a CPE out there that is not assigned to a client we want to identify it. We use the following query to do that.

SELECT distinct cpelog.mac FROM cpelog LEFT JOIN client USING (mac) WHERE client.mac IS NULL ORDER BY cpelog.mac;

Both these tables are InnoDB with indexes on the ‘mac’ column.

The explain is outlined below.

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: cpelog type: indexpossible_keys: NULL key: mac key_len: 15 ref: NULL rows: 6477129 Extra: Using index; Using temporary; Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: client type: refpossible_keys: mac key: mac key_len: 15 ref: gg.cpelog.mac rows: 1 Extra: Using where; Using index; Distinct

We currently trying to optimise the system so it can deal with about 30000 client records which would be tied to a cpelog out about 7 million records.

This query takes about 5 minutes to run at present. How do we speed that up and stop MySQL from creating TMP tables which I assume is the issue?

Also why in the first row of the explain is the list of possible keys given as
possible_keys: NULL
instead of listing ‘mac’ and the other indexes?

I have also included the output from ‘show variables;’ in the attached file. As already stated all the tables are InnoDB and we have 1GB of RAM on the box.

Any help would be greatly appreciated.