quite a simple query is taking forever to run

[steamPunk]

Hi

I have these two tables in my db

  

client (5,667 lines):

  `CT_Num` varchar(17) NOT NULL default '',
  `CT_Intitule` varchar(35) NOT NULL default '',
  `CT_Nom` varchar(35) NOT NULL default '',
  `CT_Prenom` varchar(35) NOT NULL default '',
  `CT_Civilite` varchar(17) NOT NULL,
  `CT_Adresse` varchar(35) NOT NULL default '',
  `CT_Complement` varchar(35) NOT NULL default '',
  `CT_CodePostal` varchar(9) NOT NULL default '',
  `CT_Ville` varchar(35) NOT NULL default '',
  `CT_TelPortable` varchar(21) NOT NULL default '',
  `CT_Telephone` varchar(21) NOT NULL default '',
  `CT_Email` varchar(69) NOT NULL default '',
  `Date_de_naissance` date NOT NULL default '0000-00-00',
  `ID_Client_SITE` int(11) NOT NULL auto_increment,
  `CT_Pass` varchar(16) NOT NULL,
  `CT_Encours` decimal(6,2) NOT NULL default '0.00',
  `CT_Consigne` tinyint(4) NOT NULL default '0',
  `CT_Prelevement` tinyint(4) NOT NULL default '0',
  `PL_ID_SITE` int(11) NOT NULL,
  `CT_prem_comm` tinyint(4) NOT NULL default '0',
  `mailed` tinyint(4) NOT NULL,
  `CT_RAPPEL` tinyint(4) NOT NULL,
  `CT_JOUR` tinyint(4) NOT NULL,
  `CT_NUM_RAPPEL` int(11) NOT NULL,
  `CT_LAST_RAPPEL` date NOT NULL,

comm_en (14,170 lines) :

  `CT_Client` varchar(17) NOT NULL,
  `ID_Client_SITE` int(11) NOT NULL,
  `DATE_COMMANDE` date NOT NULL,
  `DATE_LIVRAISON` date NOT NULL,
  `POINT_LIVRAISON` varchar(50) NOT NULL,
  `MESSAGE_CLIENT` varchar(600) NOT NULL,
  `DO_Piece` varchar(9) NOT NULL,
  `ID_Commande_Site` int(11) NOT NULL auto_increment,
  `PAYE` tinyint(4) NOT NULL default '0',
  `comm_temp` varchar(16) NOT NULL,
  `TIMESTAMP` bigint(20) default NULL,
  `comm_arch` tinyint(4) NOT NULL default '0',
  `comm_SAGE` text NOT NULL,

and i want to get the most recent order for each client - using the date in the DATE_LIVRAISON field.

so i thought i could use SELECT MAX(DATE_LIVRAISON) but that seems to make the whole thing run incredibly slowly

here's my query :

#
#
"SELECT t1.CT_Num, t1.ID_Client_SITE, MAX(t2.DATE_COMMANDE) AS CT_COMM FROM client AS t1 LEFT JOIN comm_en AS t2 ON t2.CT_Client=t1.CT_Num WHERE t1.CT_Email!='' GROUP BY t2.DATE_COMMANDE";
#
#

if i just do a simple select on the clients table it runs like normal so it must be either the MAX() that I'm not doing right or the LEFT JOIN

can anyone see what I'm doing wrong ?

thanks

[NogDog]

What are the indexes you've defined for those tables? I would expect you want indexes on the columns in red, below:

SELECT t1.CT_Num, t1.ID_Client_SITE, MAX(t2.DATE_COMMANDE) AS CT_COMM 
FROM client AS t1 
LEFT JOIN comm_en AS t2 ON [color=red]t2.CT_Client[/color]=[color=red]t1.CT_Num[/color] 
WHERE [color=red]t1.CT_Email[/color]!='' 
GROUP BY [color=red]t2.DATE_COMMANDE[/color]

[steamPunk]

thanks for your reply

yes i have indexes on those columns

when i paste the query into the SQL box in PhpMyAdmin it hangs for about 30 seconds and then returns the error

#2006 - MySQL server has gone away 

could it be something to do with the fact that the client table contains 5500 clients and there are over 14000 orders and i have joined the two tables ? is a MAX() a slow process ?

[steamPunk]

if you wanted to get the most recent order for each client that has a non-empty email field, how would you go about it ?