Hi,
I'm relatively new to MySQL, and am having trouble with the
optimization of some of the queries. I have two tables (T_PERSONNE and
T_DEVIS) which are supposed to be linked by a foreign key
(T_PERSONNE.PERS_ID = T_DEVIS.DEV_PERS). I've put an index on the
foreign key (DEV_PERS), and even on the combination of PERS_ID and
DEV_PERS (this worked fine for another table with a similar structure),
but when I analyse the query using EXPLAIN, the base still wants to use
an ALL join on the second table.
I'm using MySQL 3.23.53
Thanks in advance,
John.
CREATE table T_PERSONNE(
PERS_GUID varchar(32) not null unique,
PERS_NAME varchar(50) not null,
...
primary key (PERS_GUID));
CREATE index EST_RENSEIGNE_PAR_FK on T_PERSONNES(PERS_CIV);
CREATE index PERS_NAME_IDX on T_PERSONNES(PERS_NAME);
CREATE table T_DEVIS(
DEV_NUM int not null unique,
DEV_PERS varchar(32) not null,
...
primary key (DEV_NUM));
CREATE index EST_LIE_FK on T_DEVIS(DEV_TYPE);
CREATE index POSSEDE_FK on T_DEVIS(DEV_PERS);
CREATE index EST_ASSOCIE_FK on T_DEVIS(DEV_STATE);
CREATE index DEVIS_PERSONNE_IDX on T_DEVIS(DEV_PERS,DEV_NUM);
CREATE index DEVIS_PERSONNE_IDX on T_DEVIS(DEV_PERS,DEV_NUM);
mysql> explain select * from t_personnes, t_devis where
dev_pers=pers_guid and pers_name = 'DUPONT';
+-------------+------+---------------------------------+---------------+---------+-------+------+------------+
| table | type | possible_keys | key |
key_len | ref | rows | Extra |
+-------------+------+---------------------------------+---------------+---------+-------+------+------------+
| t_personnes | ref | PRIMARY,PERS_GUID,PERS_NAME_IDX | PERS_NAME_IDX
| 50 | const | 1 | where used |
| t_devis | ALL | FULL_DEVIS_IDX,POSSEDE_FK | NULL
| NULL | NULL | 3 | where used |
+-------------+------+---------------------------------+---------------+---------+-------+------+------------+
2 rows in set (0.00 sec)
--
John Ferguson Smart
Directeur Technique
Département Informatique Communicante
AACOM
email : [EMAIL PROTECTED]
---------------------------------------------------------------------
AACOM - L'Informatique communicante
120 rue du Marin Blanc - Z.I. des Paluds
13685 Aubagne Cedex
tel : 04.42.72.65.69 - fax : 04.42.72.65.68
Web : http://www.aacom.fr
---------------------------------------------------------------------
---------------------------------------------------------------------
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
