MySQL Forums
Forum List  »  Optimizer & Parser

Optimiztion of LEFT JOIN and LIMIT
Posted by: Sopace Sopcae
Date: October 05, 2010 01:33PM

Good evening!

Please, help to optimizes a query. The query takes four-five seconds.

The table "tableA" has near 100 000 rows.
The table "tableB" has near 250 000 rows (tableA -> tableB - one-to-many).
The table "tableŠ”" has near 50 rows (tableB -> tableC - one-to-one).
The table "tableD" has near 2 000 000 rows (tableB -> tableD, one-to-many).

The engines are InnoDB.

SELECT
*
FROM
(
SELECT * FROM tableA LIMIT 100
) AS a
LEFT JOIN tableB AS b ON b.a_id = a.a_id
LEFT JOIN tableC AS c ON c.c_id = b.c_id
LEFT JOIN tableD AS d ON d.b_id = b.b_id



EXPLAIN SELECT info:

id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_key: (null)
key: (null)
key_len: (null)
ref: (null)
rows: 100
extra: empty

id: 1
select_type: PRIMARY
table: tableB
type: ref
possible_key: FK_tableB_tableA_a_id
key: FK_tableB_tableA_a_id
key_len: 5
ref: a.a_id
rows: 1
extra: empty

id: 1
select_type: PRIMARY
table: tableC
type: eq_ref
possible_key: PRIMARY, UK_tableC_c_id
key: PRIMARY
key_len: 1
ref: b.c_id
rows: 1
extra: empty

id: 1
select_type: PRIMARY
table: tableD
type: ref
possible_key: FK_tableD_tableB_b_id
key: FK_tableD_tableB_b_id
key_len: 9
ref: b.b_id
rows: 3
extra: using index

id: 2
select_type: PRIMARY
table: tableA
type: ALL
possible_key: (null)
key: (null)
key_len: (null)
ref: (null)
rows: 119543
extra: empty

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimiztion of LEFT JOIN and LIMIT
3606
October 05, 2010 01:33PM
1959
October 06, 2010 09:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.