Hi !
I have a problem with the query optimizer of mysql.
When I do a this request :
select Nom FROM Facture,Client
WHERE DayOfYear(Date_De_Facture)=109 AND Year(Date_De_Facture) = 1999
AND Client.Numero = Facture.Numero_Client
it takes 7 seconds
If I use Facture STRAIGHT_JOIN Client it only takes 1 second. I also tried
using LIKE '1999-04-19' for the date.
The Facture table contains 25000 records, and the Client one 18000.
Here are the structures :
CREATE TABLE Client (
Numero int(11) DEFAULT '0' NOT NULL auto_increment,
Titre varchar(255),
Nom varchar(255),
Prenom varchar(255),
Adresse varchar(255),
Ville varchar(255),
Code_Postal varchar(255),
Province varchar(255),
Pays varchar(127) DEFAULT '' NOT NULL,
Email varchar(127) DEFAULT '' NOT NULL,
Telephone varchar(255),
Fax varchar(255),
Profession varchar(255),
Achat_Pro tinyint(4),
Numero_Entreprise int(11) DEFAULT '1' NOT NULL,
Autorise tinyint(4),
Bonus int(11) DEFAULT '0',
Banni int(11) DEFAULT '0' NOT NULL,
Euros float(10,2) DEFAULT '0.00' NOT NULL,
Autorisation_Achat int(11) DEFAULT '0' NOT NULL,
Demande_Autorisation int(11) DEFAULT '0' NOT NULL,
Note_Screening int(11) DEFAULT '0' NOT NULL,
Stamp timestamp(14),
PRIMARY KEY (Numero),
KEY Numero_Entreprise (Numero_Entreprise),
KEY Banni (Banni),
KEY Email (Email),
KEY Pays (Pays),
KEY NumBan (Numero,Banni)
);
CREATE TABLE Facture (
Numero_Facture bigint(21) DEFAULT '0' NOT NULL auto_increment,
Numero_Client int(11) DEFAULT '0' NOT NULL,
User int(11) DEFAULT '0' NOT NULL,
Backup int(11) DEFAULT '0' NOT NULL,
Mode_De_Paiement varchar(50) DEFAULT '' NOT NULL,
Date_De_Facture datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
Validation int(11) DEFAULT '1' NOT NULL,
Monnaie varchar(50) DEFAULT '' NOT NULL,
TVA float(10,2) DEFAULT '20.60' NOT NULL,
Prix_Paye double(16,4) DEFAULT '0.0000' NOT NULL,
Certificat varchar(50) DEFAULT '' NOT NULL,
Autorisation_Sligos varchar(50) DEFAULT '' NOT NULL,
Jour_Fin datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
CB varchar(30) DEFAULT '' NOT NULL,
CB_Date_Validite varchar(10) DEFAULT '' NOT NULL,
Prix_Sligos float(10,2) DEFAULT '0.00' NOT NULL,
Stamp timestamp(14),
CD_Envoye int(11) DEFAULT '0' NOT NULL,
KEY Numero_Facture (Numero_Facture),
PRIMARY KEY (Numero_Facture),
KEY Date_De_Facture (Date_De_Facture),
);
>Server: /usr/local/bin/mysqladmin Ver 7.8 Distrib 3.22.18, for pc-linux-gnulibc1 on
> i686
TCX Datakonsult AB, by Monty
Server version 3.22.18-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 41 min 38 sec
Threads: 2 Questions: 7646 Slow queries: 4 Opens: 71 Flush tables: 1 Open tables: 67
System: Linux softlnx.mhnet.fr 2.0.36 #2 Thu Apr 8 14:57:46 MET DST 1999 i686
unknown Architecture: i686
Some paths: /bin/perl /usr/bin/make /usr/bin/gmake /usr/local/bin/gcc /usr/bin/cc
GCC: Reading specs from
/usr/local/lib/gcc-lib/i686-pc-linux-gnulibc1/egcs-2.90.29/specs gcc version
egcs-2.90.29 980515 (egcs-1.0.3 release) Compilation info: CC='gcc' CFLAGS=''
CXX='c++' CXXFLAGS='' LDFLAGS='' Configure command: ./configure
--enable-assembler --without-debug Perl: This is perl, version 5.004_04 built
for i686-linux
I can use straight_join for the moment (just a little more time testing the
query), but I'm pretty sure a lot of people have the same problem, which as far
as I remember did not exact in the 3.20 version, before some big rewrites in
the optimizer.
Feel free to ask me for more details
J-F Mammet
webmaster@stripped
