Hello, I did not find anywhere the description of how MySQL processes multiple join.
I'am attaching data for my actual db but those are not nesseesary for the question. I have a db which holds the data about CD Titles. The tables are: o - info about people su - info about persons associated with particular song s - info about songs ts - the table association Titles and songs t - info about titles The EXPLAIN shows: table type possible_keys key key_len ref rows Extra o range PRIMARY,Prijmeni Prijmeni 40 9 where used; Using temporary su ref IDSkladby,IDKategorieRole,IDOsoby IDOsoby 2 o.IDOsoby 560 where used s eq_ref PRIMARY PRIMARY 3 su.IDSkladby 1 where used ts ref PRIMARY PRIMARY 3 s.IDSkladby 1 Using index t eq_ref PRIMARY PRIMARY 2 ts.IDTitulu 1 where used The query is: SELECT t.IDTitulu, t.KC, t.Nazev, t.Medium, t.Cena FROM cOsoby AS o, SkladbyUmelci AS su, Skladby AS s, TitulySkladby AS ts, Tituly AS t WHERE t.IDTitulu = ts.IDTitulu AND ts.IDSkladby = s.IDSkladby AND s.IDSkladby = su.IDSkladby AND o.IDOsoby = su.IDOsoby AND s.Nazev LIKE '%e%' AND t.Nazev LIKE '%e%' AND o.Prijmeni LIKE 'no%' AND su.IDKategorieRole = 1 GROUP BY t.IDTitulu LIMIT 100, 20 I suppose that (correct me if wrong): It creates temp. table in which it places records from o which were selected using index Prijmeni. Then it opens the su table and finds all records matching IDOsoby from o (IDPerson) - from this list it automaticalli excludes rows restricted by su.IDKategorieRole = 1. Than it opens s and using the index (IDSkladby) it looks up all records from s (songs). From the "so-far-created" table it removes all records which does not match s.Nazev LIKE '%e%'. Or is that done on the row-bases - all tables are open in one time and the temp. table is created so that one row is composed from all the tables and then it proceeds to another? Thanx much Miroslav Renda --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
