Alex
Reputation: 55
MySql queries takes long to finish
I have a classic ASP page in which I have all my clients(about 4,000),active or not, and I need to get information on them in 4 different tables. The way I'm currently doing it, my page takes 3,4,5 minutes depending on how busy the server is also.Sometimes I even get the message in asp that says "The maximum amount of time for a script to execute was exceeded".
My queries are as follow,
1. I get my clients personal info
2. I get my clients sales
3. after having that info, I do a for loop of my clients array to get other information.
In that loop I make 5 different queries.
All the information I get is necessary in my page.
What would be the best method to do all of this ?
(I was looking into views,triggers,functions/procedures)
Queries (in FRENCH): 1.
SELECT c.NoClient, c.NomClient, c.CodeClient, c.NoClientFront,c.NoVendeur,
c.VilleClient,c.DateOuverture",Telemarketing,Autre1 FROM Client c WHERE (left(c.CodeClient,1) = 'C' Or left(c.CodeClient,1) = 'N') ORDER BY c.NoClient
CREATE TABLE `client` (
`NoClient` decimal(7,0) NOT NULL DEFAULT '0',
`NoVendeur` decimal(3,0) NOT NULL DEFAULT '0',
`NomClient` varchar(35) NOT NULL DEFAULT '',
`AdresseClient` varchar(35) NOT NULL DEFAULT '',
`VilleClient` varchar(30) NOT NULL DEFAULT '',
`TelClient` varchar(10) NOT NULL DEFAULT '',
`CodeClient` char(3) NOT NULL DEFAULT '',
`PointPower` int(11) NOT NULL DEFAULT '0',
`PrevisionYonka` int(11) NOT NULL DEFAULT '0',
`PrevisionCollin` int(11) NOT NULL DEFAULT '0',
`TerritoireClient` int(3) NOT NULL DEFAULT '0',
`NoClientFront` int(7) NOT NULL DEFAULT '0',
`PersonneContact` varchar(20) NOT NULL DEFAULT '',
`CodeCredit` varchar(30) NOT NULL DEFAULT '',
`CodePostalClient` varchar(10) NOT NULL DEFAULT '',
`DateOuverture` date NOT NULL DEFAULT '0000-00-00',
`Master` int(7) NOT NULL DEFAULT '0',
`Telemarketing` varchar(20) NOT NULL DEFAULT '',
`GammeAutorisee1` char(2) NOT NULL DEFAULT '',
`GammeAutorisee2` char(2) NOT NULL DEFAULT '',
`GammeAutorisee3` char(2) NOT NULL DEFAULT '',
`GammeAutorisee4` char(2) NOT NULL DEFAULT '',
`GammeAutorisee5` char(2) NOT NULL DEFAULT '',
`MembrePower` char(1) NOT NULL DEFAULT '',
`NoteLivreur1` varchar(35) NOT NULL DEFAULT '',
`NoteLivreur2` varchar(35) NOT NULL DEFAULT '',
`PlanAVenirYonka` int(11) NOT NULL DEFAULT '0',
`PlanAVenirCollin` int(11) NOT NULL DEFAULT '0',
`Autre1` varchar(50) NOT NULL COMMENT 'Langue du client',
`Autre2` varchar(50) NOT NULL DEFAULT '',
`NoteClient` text NOT NULL,
`CompteInternet` int(1) unsigned NOT NULL,
`DEI` varchar(10) NOT NULL,
`Empty1` varchar(45) NOT NULL,
`Empty2` varchar(45) NOT NULL,
PRIMARY KEY (`NoClient`),
KEY `CodeClient` (`CodeClient`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2.
Select a.NoClient,a.Gamme,a.Vente,a.Annee,a.Mois,c.Dateouverture" from AchatClient a, Client c where c.NoClient = a.NoClient And a.Annee <= " & AnneeRanking
And a.Annee >= " & AnneeRanking - 1 order by a.NoClient
CREATE TABLE `achatclient` (
`NoClient` int(10) DEFAULT NULL,
`NoVendeur` int(3) DEFAULT NULL,
`Gamme` char(1) DEFAULT NULL,
`G1` char(1) DEFAULT NULL,
`G2` char(1) DEFAULT NULL,
`Annee` int(4) DEFAULT NULL,
`Mois` int(2) DEFAULT NULL,
`Vente` float(10,2) DEFAULT NULL,
KEY `NoClient` (`NoClient`,`NoVendeur`),
KEY `Vente` (`NoClient`,`Annee`,`Mois`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
in my FOR loop
a)
Select DatePlanification from planification where NoClient=" & client(i)
And DatePlanification <= '" & todayDate & "' And Etat=0 Order by DatePlanification DESC limit 1
CREATE TABLE `planification` (
`NoPlanification` decimal(10,0) NOT NULL DEFAULT '0',
`IDUsager` varchar(30) DEFAULT NULL,
`NoClient` decimal(7,0) DEFAULT NULL,
`DatePlanification` date DEFAULT NULL,
`HeurePlanDebut` varchar(5) NOT NULL DEFAULT '',
`HeurePlanFin` varchar(5) NOT NULL DEFAULT '',
`LieuxPlanification` varchar(50) NOT NULL DEFAULT '',
`TypePlanification` varchar(50) NOT NULL DEFAULT '',
`NomPlanification` varchar(250) NOT NULL DEFAULT '',
`Commentaire` text NOT NULL,
`Confirmation` int(1) unsigned zerofill NOT NULL DEFAULT '0',
`TypeContact` varchar(50) NOT NULL DEFAULT '',
`GammeContact` varchar(50) NOT NULL DEFAULT '',
`DetailPersonnel` text NOT NULL,
`DetailPublic` text NOT NULL,
`DateEntree` date DEFAULT NULL,
`HeureEntree` time DEFAULT NULL,
`DateModifie` date DEFAULT NULL,
`HeureModifie` time DEFAULT NULL,
`Autre1` varchar(50) NOT NULL DEFAULT '',
`Autre2` varchar(50) NOT NULL DEFAULT '',
`Etat` int(1) NOT NULL DEFAULT '0',
`DateCheckIn` varchar(15) DEFAULT NULL,
`TimeCheckIn` varchar(15) DEFAULT NULL,
`PositionCheckIn` varchar(100) DEFAULT NULL,
`DateCheckOut` varchar(15) DEFAULT NULL,
`TimeCheckOut` varchar(15) DEFAULT NULL,
`PositionCheckOut` varchar(100) DEFAULT NULL,
`Empty1` varchar(45) DEFAULT NULL,
`Empty2` varchar(45) DEFAULT NULL,
PRIMARY KEY (`NoPlanification`),
KEY `IDUsager` (`IDUsager`,`DatePlanification`),
KEY `DatePlanification` (`DatePlanification`),
KEY `NoClient` (`NoClient`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
b)
Select DateVisite from visite_kterm where NoClient = " & Client(i)
order by DateVisite DESC limit 1
CREATE TABLE `visite_kterm` (
`NoClient` int(7) DEFAULT NULL,
`DateVisite` date DEFAULT NULL,
`SequenceVisite` char(2) DEFAULT NULL,
`Usager` varchar(20) DEFAULT NULL,
`Description_1` varchar(72) DEFAULT NULL,
`Description_2` varchar(72) DEFAULT NULL,
`Description_3` varchar(72) DEFAULT NULL,
`Description_4` varchar(72) DEFAULT NULL,
`Description_5` varchar(72) DEFAULT NULL,
`Description_6` varchar(72) DEFAULT NULL,
`Description_7` varchar(72) DEFAULT NULL,
KEY `NoClient` (`NoClient`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
C)
Select DatePlanification from Planification where NoClient=" & Client(i)
And DatePlanification > '" & answerFromA & "' And Etat=0
Order by DatePlanification ASC limit 1;
d)SELECT DateFacture FROM listefactures where NoClient=" & client(i)
CREATE TABLE `listefactures` (
`NoFacture` varchar(7) NOT NULL DEFAULT '',
`NoPrise` int(6) DEFAULT NULL,
`NoClient` int(10) DEFAULT NULL,
`PrisePar` varchar(10) DEFAULT NULL,
`DateFacture` date DEFAULT NULL,
`AdresseEnvoie` text,
`Reference` varchar(10) DEFAULT NULL,
`SousTotal` float(10,2) DEFAULT NULL,
`Total` float(10,2) DEFAULT NULL,
`Terme` varchar(20) DEFAULT NULL,
`TaxePro` float(10,2) DEFAULT NULL,
`TaxeFed` float(10,2) DEFAULT NULL,
`Transporteur` varchar(20) DEFAULT NULL,
`FraisTransport` float(10,2) DEFAULT NULL,
`FraisManutention` float(10,2) DEFAULT NULL,
`Ouverture` char(1) DEFAULT NULL,
`FacturePower` char(1) DEFAULT NULL,
`CodeCredit` int(3) unsigned NOT NULL,
`RepOuverture` varchar(5) NOT NULL,
PRIMARY KEY (`NoFacture`),
KEY `NoClient` (`NoClient`,`Ouverture`),
KEY `NoPrise` (`NoPrise`),
KEY `DateFacture` (`DateFacture`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
e)
Select Email from SDS where NoCompte = " & Client(i)
And Email like '%@%' And Email like '%.%';
CREATE TABLE `sds` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NoCompte` int(11) NOT NULL DEFAULT '0',
`Email` varchar(100) DEFAULT NULL,
`SiteWeb` varchar(255) DEFAULT NULL,
`NomProprietaire` varchar(100) DEFAULT NULL,
`ageproprietaire` varchar(20) DEFAULT NULL,
`StatutProprietaire` int(11) DEFAULT NULL,
`NomProprietaireDuLocataire` text,
`Localisation` int(11) DEFAULT NULL,
`Situation` varchar(30) NOT NULL DEFAULT '',
`ProprioEstEstheticienne` int(11) DEFAULT NULL,
`Travailencabine` int(11) NOT NULL DEFAULT '0',
`ProprioDepuis` varchar(5) NOT NULL DEFAULT '',
`NomPersonneContact` varchar(100) DEFAULT NULL,
`FonctionPersonneContact` int(11) DEFAULT NULL,
`NbEstheticienne` int(11) DEFAULT NULL,
`NbEmploye` int(11) DEFAULT NULL,
`NbCabineFacial` int(11) DEFAULT NULL,
`NbCabineCorporel` int(11) DEFAULT NULL,
`NbCabineAutre` int(11) DEFAULT NULL,
`NbCabineDouche` int(11) DEFAULT NULL,
`NbCabineBain` int(11) NOT NULL DEFAULT '0',
`NbCabineLitSolaire` int(11) NOT NULL DEFAULT '0',
`SoinsOfferts` int(11) DEFAULT NULL,
`Competition1` varchar(30) DEFAULT NULL,
`Competition2` varchar(30) DEFAULT NULL,
`Competition3` varchar(30) DEFAULT NULL,
`Competition4` varchar(30) DEFAULT NULL,
`Competition5` varchar(30) DEFAULT NULL,
`PotentielClient` varchar(15) DEFAULT NULL,
`EtatVitrine` int(11) DEFAULT NULL,
`EtatComptoireVitre` int(11) DEFAULT NULL,
`EtatEtalageMur` int(11) DEFAULT NULL,
`SurfaceIncitante` int(11) DEFAULT NULL,
`PopulationVille` int(11) DEFAULT NULL,
`NbCliniqueExistante` int(11) DEFAULT NULL,
`NbCliniqueNotre` int(11) DEFAULT NULL,
`ParticulariteVille` text NOT NULL,
`DateMAJ` date DEFAULT NULL,
`Email1` varchar(100) NOT NULL DEFAULT '',
`Contact1` varchar(50) NOT NULL DEFAULT '',
`titre1` varchar(50) NOT NULL DEFAULT '',
`Email2` varchar(100) NOT NULL DEFAULT '',
`Contact2` varchar(50) NOT NULL DEFAULT '',
`titre2` varchar(50) NOT NULL DEFAULT '',
`Email3` varchar(100) NOT NULL DEFAULT '',
`Contact3` varchar(50) NOT NULL DEFAULT '',
`titre3` varchar(50) NOT NULL DEFAULT '',
`Email4` varchar(100) NOT NULL DEFAULT '',
`Contact4` varchar(50) NOT NULL DEFAULT '',
`titre4` varchar(50) NOT NULL DEFAULT '',
`statutProspect` int(3) unsigned NOT NULL,
`dei` int(4) unsigned NOT NULL DEFAULT '0',
`deivalue` float NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`,`NoCompte`)
) ENGINE=MyISAM AUTO_INCREMENT=7356 DEFAULT CHARSET=latin1
Upvotes: 0
Views: 151
Answers (1)
G-Nugget
Reputation: 8846
I combined all of your queries into one. I made a few assumptions like that you only want one record per client. Variables that need to be added are in curly braces.
SELECT NoClient, Gamme, Vente, Annee, Mois, Dateouverture, NomClient, CodeClient,
NoClientFront, NoVendeur, VilleClient, DateOuverture, Telemarketing, Autre1,
MAX(DatePlanification) AS DatePlanification, MAX(DateVisite ) AS DateVisite, Email
FROM Client
JOIN AchatClient USING (NoClient)
JOIN planification USING (NoClient)
JOIN visite_kterm USING (NoClient)
JOIN DateFacture USING (NoClient)
LEFT JOIN SDS ON (NoClient = NoCompte)
WHERE (CodeClient LIKE 'C%' OR CodeClient LIKE 'N%')
AND Annee BETWEEN {AnneeRanking} AND {AnneeRanking - 1}
AND DatePlanification <= {todayDate}
AND Etat=0
AND Email like '%@%' AND Email like '%.%'
GROUP BY NoClient
ORDER BY NoClient
The query would benefit from indexes as listed below:
SDS - single index on `NoCompte`
planification - make the `NoClient` index composite like (`NoClient`, `DatePlanification`)
achatclient - single index on `Annee`
Also, make all of the NoClient columns one of the INT types.
Using a single query like this should be much faster than tons of serial queries. You might need to switch some of the JOINs to LEFT JOINs depending on the data.
Upvotes: 2
Related Questions
- MySQL query takes 2 minutes
- MySQL Query Taking forever to execute
- Mysql Query takes too long to execute. Why?
- Mysql - Taking too much time to execute queries
- mysql query execution consuming time
- Query Taking Longer Time to Run
- Query taking long time to execute
- query taking long to execute
- Mysql Very Long Time in Query Execution
- MySQL query takes time to run