CODEWITHSUNDEEP
mysql
martincarlin87
martincarlin87

Reputation: 11062

Poor Performance from MySQL JOIN - How to Make Improvements?

A bit of a generic question title but I have the following query:

SELECT t.from_number, COUNT(*) AS calls
FROM t
WHERE t.organisation_id = 999
AND t.direction = 'inbound'
AND t.start_time BETWEEN '2014-03-26' AND NOW()
AND t.from_number != ''
GROUP BY t.from_number
ORDER BY calls DESC LIMIT 20

and it executes in 488ms.

However, aswell as retrieving the data from that table I need to lookup who the number belongs to.

SELECT t.from_number, COUNT(*) AS calls
FROM t
LEFT JOIN n on CONCAT('44', n.number) = t.from_number
WHERE t.organisation_id = 999
AND t.direction = 'inbound'
AND t.start_time BETWEEN '2014-03-26' AND NOW()
AND t.from_number != ''
GROUP BY t.from_number
ORDER BY calls DESC LIMIT 20

As soon as I add the JOIN the query execution time jumps up to anything from 8 - 12 seconds and that's only to find the organisation that the number belongs to, I'd need yet another join after that to retrieve the organisation name from the organisations table.

The cardinality of t and n are > 2,000,000 and ~ 63,000 respectively, and, as you can guess from above, the numbers are stored slightly differently in each:

t stores numbers as 123456789 since the country code (44) is stored in a separate column but n stores numbers as 44123456789 which is why I need to use the CONCAT but I didn't think this would affect performance since it's not in the WHERE clause.

As far as I can tell, I have indexed the important columns in each table.

Are there any suggestions on how I can improve the performance of queries when it comes to these tables?

Update

EXPLAIN output added

id, select_type, table, possible_keys, key, key_len, ref, rows, Extra

1   SIMPLE  t   index_merge organisation_id,start_time,direction,from_number    organisation_id,direction   4,13    NULL    4174    Using intersect(organisation_id,direction); Using where; Using temporary; Using filesort
1   SIMPLE  n   index   NULL    number  768 NULL    62759   Using index

Upvotes: 0

Views: 51

Answers (1)

Oscar Pérez
Oscar Pérez

Reputation: 4397

The problem is on the JOIN clause:

LEFT JOIN n on CONCAT('44', n.number) = t.from_number

It is joining the tables using the result of the function CONCAT('44', n.number).

Some databases (as Oracle), can create an index based on a funcion, but others (as MySQL) cannot. So, it cannot use any index on table n to make the join.

A solution would be to create a new column on n with the result of the used function and to index it.

You could use a code similar to:

ALTER TABLE n ADD COLUMN extended_number varchar(128) null;

UPDATE n
   SET extended_number = CONCAT('44', number);

CREATE INDEX ext_numb_idx 
          ON n.extended_number;

After this, modify the JOIN clause of the query:

   SELECT t.from_number, COUNT(*) AS calls
     FROM t
LEFT JOIN n on n.extended_number = t.from_number
    WHERE t.organisation_id = 999
      AND t.direction = 'inbound'
      AND t.start_time BETWEEN '2014-03-26' AND NOW()
      AND t.from_number != ''
 GROUP BY t.from_number
 ORDER BY calls DESC LIMIT 20

Then MySQL will use the newly created index and will execute the query much faster.

Upvotes: 1

Related Questions