[SOLVED] JOIN Query Optimalization MySQL 5.0.27 (MyISAM tables)

[Linvo]

Hello,

I'm studying optimalization of MySQL queries, this time the SELECT query with the use of joins.

I'v attached a word document with the result of an explain of a select query with the use of LEFT JOIN and the WHERE part.

I like to have some critic/remarks how this query is formulated. Maybe there are alternatives.

Thanks for reply's.

[attachment deleted by admin]

[hvle]

This query appears to be long and took a while to get thru.

The only improvment that I see is reducing the redundancies in the under WHERE condition:
for instance, this part:
LEFT JOIN hoofdafdeling ON afdeling.hoofdafdeling_id=hoofdafdeling.id LEFT JOIN unit ON hoofdafdeling.unit_id=unit.id

is already have the condition "hoofdafdeling.unit_id=unit.id"
and therefore you do not need to repeat it under WHERE.

You have a quite a few of this issues.

[Linvo]

Thanks for your reply HVLE,

I've omitted the redundancies and made the query more readable.
See the attached doc.

Furthermore, I changed the LEFT JOIN in JOIN because I'm not interested in records with the NULL value.

Maybe there's more to optimize?



[attachment deleted by admin]

[hvle]

hi Linvo,

The query look much better!
It is a good idea to test and see if it gave the correct results.

[Linvo]

It does!

Thanks.