CODEWITHSUNDEEP
mysqlsql
JonoB
JonoB

Reputation: 5887

MySQL query optimisation - existing query runs too slowly

I'm trying to optimise the following query, but its running really slowly for me:

SELECT `trans_email`.*
, `email_statuses`.`recipient`, `email_statuses`.`status_id`, `email_statuses`.`message`, `email_statuses`.`status_received_at`
, `trans`.`doc`
FROM `trans_email`
LEFT JOIN `email_statuses` ON `trans_email`.`id` = `email_statuses`.`trans_email_id`
LEFT JOIN `trans` ON `trans_email`.`trans_id` = `trans`.`id`
WHERE `trans_email`.`type_id` = 0 AND `trans`.`company_id` = 1 
ORDER BY `email_statuses`.`status_received_at` DESC
LIMIT 25 OFFSET 0

25 rows in set (4.87 sec)

Here's the output from the EXPLAIN: id: 1 select_type: SIMPLE table: trans_email partitions: NULL type: ALL possible_keys: trans_id key: NULL key_len: NULL ref: NULL rows: 769970 filtered: 10.00 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: trans partitions: NULL type: eq_ref possible_keys: PRIMARY,fk_trans_company,co_del_drft_type,co_drft_del_utc key: PRIMARY key_len: 4 ref: trans_email.trans_id rows: 1 filtered: 5.00 Extra: Using where *************************** 3. row *************************** id: 1 select_type: SIMPLE table: email_statuses partitions: NULL type: ref possible_keys: email_statuses_trans_email_id_foreign key: email_statuses_trans_email_id_foreign key_len: 4 ref: trans_email.id rows: 2 filtered: 100.00 Extra: NULL 3 rows in set, 1 warning (0.00 sec)

As far as I can see, everything is correctly indexed. (Note that trans_email.type_id is actually a bool, and has therefore not been indexed.)

Upvotes: 0

Views: 35

Answers (1)

Amadan
Amadan

Reputation: 198388

You use LEFT JOIN though you require a non-NULL field in the joined table in your WHERE.

`trans`.`company_id` = 1 cannot be true if the LEFT JOIN produces a NULL row for trans, so none of the extra rows produced by LEFT JOIN (compared to inner JOIN) would be admitted in the end result.

With a LEFT JOIN, you produce at least 769970 rows (i.e. at least one per row in trans_email), then you trim them down to 25. If you had an inner JOIN instead, you would immediately cut down to 50ish rows (assuming roughly equal distribution of the boolean column) just from your primary index, then cut down to 25 with your boolean condition.

EDIT: Changing the other LEFT JOIN (the email_statuses one) will actually change your results, unless you have full coverage in your email_statuses table, and it shouldn't really affect your runtime that much, once the other LEFT JOIN is gone, so feel free to leave that LEFT JOIN as is.

Thus - try this one (only one word slimmer):

SELECT `trans_email`.*
, `email_statuses`.`recipient`, `email_statuses`.`status_id`, `email_statuses`.`message`, `email_statuses`.`status_received_at`
, `trans`.`doc`
FROM `trans_email`
JOIN `email_statuses` ON `trans_email`.`id` = `email_statuses`.`trans_email_id`
LEFT JOIN `trans` ON `trans_email`.`trans_id` = `trans`.`id`
WHERE `trans_email`.`type_id` = 0 AND `trans`.`company_id` = 1 
ORDER BY `email_statuses`.`status_received_at` DESC
LIMIT 25 OFFSET 0

Upvotes: 1

Related Questions