I have a query that takes ages ... my app remains in the first sqlite_step for more than 30 minutes (with 100% CPU time). Query is:
SELECT reseaux.name, reseaux.id_reseau, reseaux.insee_id, panneaux.insee_id, panneaux .reseau_id ,insee .commune ,afficheurs .societe,reseaux.name,reseaux.nb,reseaux.tarif,agglomerations.commune AS aggloName,agglomerations.codeInsee AS aggloInsee ,reseaux .deroulant ,reseaux .eclairage ,reseaux .emprise ,reseaux.partDeVoie,insee.population,SUM(reseaux_insee.population) AS popTouch,SUM(formats.trivision) AS trivision, insee.agglo_id, agglomerations.id_agglomeration FROM reseaux,reseaux_insee,panneaux,formats,typeFormats,afficheurs,insee, agglomerations WHERE insee.agglo_id = agglomerations.id_agglomeration AND panneaux.reseau_id = reseaux.id_reseau AND (panneaux.insee_id = 38656) AND formats.reseau_id = reseaux.id_reseau AND ((formats.startDate >= '2008-02-09' AND formats.endDate <= '2008-02-16') OR (formats.endDate >= '2008-02-09' AND formats.startDate <= '2008-02-09') OR (formats.endDate >= '2008-02-16' AND formats.startDate <= '2008-02-16') OR (formats.startDate <= '2008-02-09' AND formats.endDate >= '2008-02-16')) AND formats.typeFormat_id = typeFormats.id_typeFormat AND afficheurs.id_afficheur = reseaux.afficheur_id AND insee.id_insee = reseaux.insee_id GROUP BY reseaux.id_reseau ORDER BY reseaux.name and tables are: CREATE TABLE afficheurs (societe VarChar NOT NULL , Prenom VarChar , nom VarChar , Adresse1 VarChar , Adresse2 VarChar , Code_Postal VarChar , Ville VarChar NOT NULL , Telephone VarChar , Fax VarChar , code Binary NOT NULL , Civilite VarChar , id_afficheur Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, groupe_id Integer NOT NULL ); CREATE TABLE agglomerations (commune VarChar NOT NULL , codeUU VarChar NOT NULL , population Integer NOT NULL , id_agglomeration Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, codeInsee VarChar ); CREATE TABLE formats (id_format Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, reseau_id Integer , typeFormat_id Integer , startDate Date , endDate Date , nb Integer , deroulant Integer , trivision Integer , afficheur_id Integer , groupe_id Integer ); CREATE TABLE insee (commune VarChar NOT NULL , codeInsee VarChar NOT NULL , population Integer NOT NULL , region_id Binary NOT NULL , ze_id Integer NOT NULL , lgt Float , lat Float , Coeficient_Couverture Double , Coeficient_Repetition Double , Panneaux_commune SmallInt , departement_id Integer NOT NULL , id_insee Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, agglo_id Integer , arrondissement_id Integer , codeZe VarChar , sinLat Float , cosLat Float ); CREATE TABLE panneaux (Code_Panneau VarChar NOT NULL , Code_Reseau_Unique VarChar NOT NULL , Code_Insee VarChar NOT NULL , adresse1 VarChar , Adresse2 VarChar , Code_Insee_Afficheur VarChar NOT NULL , Commune VarChar , coordX Double , coordY Double , Angle SmallInt , noRue VarChar , Code_Afficheur Binary NOT NULL , Commune_Insee VarChar , Points_Qualite SmallInt , Points_Trafic SmallInt , isole Binary NOT NULL , eclairage Binary NOT NULL , Trivision Binary NOT NULL , Deroulant Binary NOT NULL , Comptage_Trafic Integer , Format VarChar NOT NULL , Date_Actualisation Date , Perpendiculaire_Panneau SmallInt , id_panneau Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, reseau_id Integer , insee_id Integer , lat Float , lgt Float , typeFormat_id Integer , nb Integer , afficheur_id Integer , groupe_id Integer ); CREATE TABLE reseaux (codeInsee VarChar NOT NULL , name VarChar NOT NULL , Code_Reseau_Afficheur VarChar NOT NULL , Match_Code VarChar NOT NULL , Code_Reseau_Unique VarChar NOT NULL , Afficheur VarChar NOT NULL , Format_General VarChar NOT NULL , Format1 VarChar NOT NULL , format2 VarChar , nb Integer , Panneaux_Format1 SmallInt , Panneaux_Format2 SmallInt , deroulant Integer , Panneaux_Deroulants_Format2 SmallInt , trivis Integer , Panneaux_Trivision_Format2 SmallInt , tarif Integer , Monnaie SmallInt , Prix_Panneau_Jour Double , Jour_de_Depart VarChar , Duree SmallInt , Type_Panneau VarChar , Concept VarChar , Vente VarChar , intraVille Integer , intraAgglo Integer , Population_Touchee Integer , emprise Double , partDeVoie Double , couvCESP Double , repCESP Double , grpCESP SmallInt , odvCESP Integer , couvSpecifique Double , repSpecifique Double , grpSpecifique SmallInt , odvSpecifique Integer , Qualite SmallInt , Trafic SmallInt , Points_Qualite_Trafic Binary , isolement Double , eclairage Integer , Categorie VarChar , Debut_Reseau Date , Fin_Reseau Date , updateDate Date , Memorisation Double , Non_recopie_adresse Binary , gamme VarChar , Groupe VarChar , Nom_Original VarChar , Code_UU VarChar , id_reseau Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, afficheur_id Integer NOT NULL , insee_id Integer , univers_id Integer , format_id Integer , couvAffi Double , repAffi Double , gprAffi SmallInt , odvAffi Integer , gamme_id Integer , vente_id Integer , type_id Integer , popTouche Integer , affiniteAffimetrie Float , updateEDI VarChar NOT NULL , groupe_id Integer ); CREATE TABLE reseaux_insee (id_reseau_insee Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, reseau_id Integer , insee_id Integer , population Integer , panneau Integer ); CREATE TABLE typeformats (id_typeFormat Integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, format VarChar NOT NULL ); Can someone suggest me the best index/indexes to use for such a query? or the best way to rewrite it in a way more manageable by sqlite? I really appreciate your help. Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
