List:General Discussion« Previous MessageNext Message »
From:J-F Mammet Date:April 19 1999 1:48pm
Subject:Query Optimizer Problem ?
View as plain text  
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
Thread
Query Optimizer Problem ?J-F Mammet19 Apr
  • where dbs residepete collins19 Apr
    • Re: where dbs resideMike Goddard19 Apr
      • Re: where dbs residepete collins19 Apr
      • help! restoringpete collins19 Apr
  • Altavista-like search on part of DBStan P. van de Burgt19 Apr