Hi folks, I used the EXPLAIN-statement to optimize one of my querys (Its been the first time I used it...).
I don't understand one of the results given by SELECT. At first, my statement: SELECT zutaten.id, zutaten.name, hersteller.name, einheiten.lang, zutaten_gruppen.name FROM zutaten JOIN hersteller JOIN einheiten JOIN zutaten_gruppen WHERE zutaten.name LIKE '%$suchbegriff%' AND einheiten.id = zutaten.einheit_id AND hersteller.id = zutaten.hersteller_id AND zutaten_gruppen.id = zutaten.gruppe_id This one should give me to each 'zutaten' matching the LIKE-statement the 'einheiten', 'hersteller' and 'zutaten_gruppen' names. Well, actually it does :) But when I use the EXLAIN-statement on this SELECT-statement there is a thing I don't understand: table type possible_keys key key_len ref rows Extra zutaten ALL NULL NULL NULL NULL 11 where used hersteller ALL PRIMARY NULL NULL NULL 3 where used einheiten eq_ref PRIMARY PRIMARY 1 zutaten.einheit_id 1 zutaten_gruppen eq_ref PRIMARY PRIMARY 2 zutaten.gruppe_id 1 Why does MySQL not use the PRIMARY to find the entrys matching hersteller.id = zutaten.hersteller_id? And why does it tell me "where used" (and the last two don't)? Here are the table structures (Reduced to the fields used in the statement) CREATE TABLE zutaten ( id smallint(5) unsigned NOT NULL auto_increment, name varchar(80) NOT NULL default '', einheit_id tinyint(3) unsigned NOT NULL default '1', hersteller_id smallint(5) unsigned NOT NULL default '1', gruppe_id smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (id), FULLTEXT KEY name (name) ) TYPE=MyISAM; CREATE TABLE zutaten_gruppen ( id smallint(5) unsigned NOT NULL auto_increment, name varchar(50) NOT NULL default '', PRIMARY KEY (id), KEY name (name) ) TYPE=MyISAM; CREATE TABLE hersteller ( id smallint(5) unsigned NOT NULL auto_increment, name varchar(60) NOT NULL default '', PRIMARY KEY (id) ) TYPE=MyISAM; CREATE TABLE einheiten ( id tinyint(3) unsigned NOT NULL auto_increment, kurz varchar(5) NOT NULL default '', PRIMARY KEY (id), KEY kurz (kurz) ) TYPE=MyISAM; Sorry for the not self-explanatory table- and fieldnames. regards Marcus --------------------------------------------------------------------- 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
