Hi,
I can't seem to find any problems, except for the few duplicate indexes in
tbl_klanten, tbl_v_levering, tbl_v_bestelbon - when you have PRIMARY KEY (klant_id)
you don need to add KEY klant_id (klant_id).
I created the tables on a 4.0.12 server and the EXPLAIN returned EQ_REF on both
queries. Here is the EXPLAIN otput
mysql> EXPLAIN SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_levering l ON l.ver_lev_id
= b.batch_lever_id LEFT JOIN tbl_klanten k ON k.klant_id = l.ver_lev_klant_id;
+-------+--------+---------------+---------+---------+--------------------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+---------------+---------+---------+--------------------+------+-------+
| b | ALL | NULL | NULL | NULL | NULL | 2 |
|
| l | eq_ref | PRIMARY | PRIMARY | 4 | b.batch_lever_id | 1 |
|
| k | eq_ref | PRIMARY | PRIMARY | 4 | l.ver_lev_klant_id | 1 |
|
+-------+--------+---------------+---------+---------+--------------------+------+-------+
3 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_bestelbon bb ON bb.ver_id =
b.batch_bestel_id LEFT JOIN tbl_klanten k1 ON k1.klant_id = bb.ver_klant_id;
+-------+--------+---------------+---------+---------+-------------------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+---------------+---------+---------+-------------------+------+-------+
| b | ALL | NULL | NULL | NULL | NULL | 2 |
|
| bb | eq_ref | PRIMARY | PRIMARY | 4 | b.batch_bestel_id | 1 |
|
| k1 | eq_ref | PRIMARY | PRIMARY | 4 | bb.ver_klant_id | 1 |
|
+-------+--------+---------------+---------+---------+-------------------+------+-------+
3 rows in set (0.00 sec)
So the last thing I could think of is putting some brackets to define the order in
which the joins will be made like
SELECT * FROM (tbl_v_batch b LEFT JOIN tbl_v_levering l ON l.ver_lev_id =
b.batch_lever_id) LEFT JOIN tbl_klanten k ON k.klant_id = l.ver_lev_klant_id;
HTH
Dobromir Velev
[EMAIL PROTECTED]
----- Original Message -----
From: Koen Van Mulders
To: Dobromir Velev
Sent: Thursday, December 18, 2003 17:52
Subject: Re: Problem with EQ_REF and ALL
Please excuse me if this comes to your personal mailbox,
i have no clue on how to use these lists :-)
I am proud I could post my problem, I don't know how to reply to it :-(.
Anyway, here are the table structures :
-----------------------------------------------------------------------------------------------------------
#
# Tabel structuur voor tabel `tbl_klanten`
#
CREATE TABLE tbl_klanten (
klant_id int(11) NOT NULL auto_increment,
klant_nummer int(11) NOT NULL default '0',
klant_bedrijfsnaam text NOT NULL,
klant_voornaam tinytext NOT NULL,
klant_achternaam tinytext NOT NULL,
klant_straat_nr text NOT NULL,
klant_postnr text NOT NULL,
klant_stad text NOT NULL,
klant_tel text NOT NULL,
klant_fax text NOT NULL,
klant_gsm text NOT NULL,
klant_email text NOT NULL,
klant_btw text NOT NULL,
klant_hr text NOT NULL,
klant_specialisatie text NOT NULL,
klant_q_assistenten smallint(6) NOT NULL default '0',
klant_q_tandartsen smallint(6) NOT NULL default '0',
klant_openingsuren text NOT NULL,
klant_active enum('0','1') NOT NULL default '1',
klant_vertegenwoordiger int(11) NOT NULL default '0',
klant_riziv text NOT NULL,
klant_btw_plichtig enum('1','2') NOT NULL default '1',
klant_type text NOT NULL,
klant_aanspreektitel text NOT NULL,
klant_taal text NOT NULL,
klant_betaaltermijn int(4) NOT NULL default '0',
klant_bank text NOT NULL,
klant_krediet text NOT NULL,
klant_korting text NOT NULL,
klant_creatie_id text NOT NULL,
klant_creatie_datum text NOT NULL,
klant_creatie_ok_id text NOT NULL,
klant_laatste_edit text NOT NULL,
klant_cat_id int(11) NOT NULL default '0',
klant_type_klant enum('KLANT','FILIAAL','BEDRIJF') NOT NULL default 'KLANT',
klant_filiaal varchar(20) NOT NULL default '',
klant_vertegenwoordiger_ok enum('0','1') NOT NULL default '1',
PRIMARY KEY (klant_id),
KEY klant_id (klant_id)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Tabel structuur voor tabel `tbl_v_batch`
#
CREATE TABLE tbl_v_batch (
batch_id int(11) NOT NULL auto_increment,
batch_type enum('B','L','F','C','FL','S') NOT NULL default 'B',
batch_stock_count enum('0','1') NOT NULL default '0',
batch_aan_id int(11) NOT NULL default '0',
batch_bestel_id int(11) NOT NULL default '0',
batch_b_detail_id int(11) NOT NULL default '0',
batch_lever_id int(11) NOT NULL default '0',
batch_fac_id int(11) NOT NULL default '0',
batch_cn_id int(11) NOT NULL default '0',
batch_pers_id int(11) NOT NULL default '0',
batch_verpakker_pers_id int(11) NOT NULL default '0',
batch_prod_id varchar(20) NOT NULL default '0',
batch_aantal int(11) NOT NULL default '0',
batch_prijs float NOT NULL default '0',
batch_te_leveren float NOT NULL default '0',
batch_te_factureren int(11) NOT NULL default '0',
batch_crediteerbaar int(11) NOT NULL default '0',
batch_voorgestelde_prijs float default NULL,
batch_geannuleerd float NOT NULL default '0',
PRIMARY KEY (batch_id),
KEY batch_aan_id (batch_aan_id),
KEY batch_bestel_id (batch_bestel_id),
KEY batch_b_detail_id (batch_b_detail_id),
KEY batch_lever_id (batch_lever_id),
KEY batch_fac_id (batch_fac_id),
KEY batch_cn_id (batch_cn_id),
KEY batch_prod_id (batch_prod_id)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Tabel structuur voor tabel `tbl_v_bestelbon`
#
CREATE TABLE tbl_v_bestelbon (
ver_id int(10) unsigned NOT NULL auto_increment,
ver_klant_id int(11) NOT NULL default '0',
ver_pers_id int(11) NOT NULL default '0',
ver_agenda_id int(11) NOT NULL default '0',
ver_datum int(11) NOT NULL default '0',
ver_opmerking mediumtext NOT NULL,
ver_leveradres_id int(11) NOT NULL default '0',
ver_afgesloten enum('0','1') NOT NULL default '0',
PRIMARY KEY (ver_id),
KEY ver_id (ver_id)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Tabel structuur voor tabel `tbl_v_levering`
#
CREATE TABLE tbl_v_levering (
ver_lev_id int(11) NOT NULL auto_increment,
ver_lev_klant_id int(11) NOT NULL default '0',
ver_lev_bestemming_id int(11) NOT NULL default '0',
ver_lev_trans_id int(11) NOT NULL default '0',
ver_lev_datum int(11) NOT NULL default '0',
ver_lev_opmerking mediumtext NOT NULL,
ver_lev_pers_id int(11) NOT NULL default '0',
ver_lev_afgesloten enum('0','1') NOT NULL default '0',
PRIMARY KEY (ver_lev_id),
KEY ver_lev_id (ver_lev_id)
) TYPE=MyISAM;
Tnx for the quick reply
Hope you can help me...
----- Original Message -----
From: Dobromir Velev
To: Koen Van Mulders ; [EMAIL PROTECTED]
Sent: Thursday, December 18, 2003 4:36 PM
Subject: Re: Problem with EQ_REF and ALL
Can you please send the tables structure. This happens when the fields you
are using in the JOIN clause are with different types fro example when
trying to join a VARCHAR with INT
Dobromir Velev
[EMAIL PROTECTED]
----- Original Message -----
From: "Koen Van Mulders" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, December 18, 2003 16:54
Subject: Problem with EQ_REF and ALL
I think they are all indexed, yes.
I have an index on :
bb.ver_id
b.batch_bestel_id
k.klant_id (k1 is the same)
b.batch_lever_id
l.ver_lev_id
Tnx in advance
