Announcement

Announcement Module
Collapse
No announcement yet.

LEFT JOIN SELECT taking ages to run

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • LEFT JOIN SELECT taking ages to run

    Hi

    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.
    Tom
Working...
X