Re: [sqlite] 3 tables in relation, really slow ...

[John LeSueur]

Andrea Giammarchi wrote:
----- Original Message ----- 
From: "Simon Berthiaume"
 

Have you tried to change the order of the conditions in the WHERE
clause? I don't know the internal working of SQLite so they might
actualy give worst results. You can try something like that:
SELECT date_ext.mydate as MyDate, city_ext.city as MyCity,
number_ext.mynum as MyNumber
FROM city_ext, number_ext, date_ext
WHERE date_ext.mydate = ( SELECT MAX( date_ext.mydate ) FROM date_ext )
   AND number_ext.mydate = date_ext.id
   AND number_ext.city = city_ext.id
ORDER BY city_ext.city, number_ext.position;
   

---------------------------------------------------
... tried but it's the same time ....

 

Or something a bit more weird like
SELECT date_maxed.mydate as MyDate, city_ext.city as MyCity,
number_ext.mynum as MyNumber
FROM city_ext, number_ext, (SELECT * FROM date_ext WHERE date_ext.mydate
= ( SELECT MAX( date_ext.mydate ) FROM date_ext )) AS date_maxed
WHERE number_ext.mydate = date_maxed.id
   AND number_ext.city = city_ext.id
ORDER BY city_ext.city, number_ext.position;
Tell me if you get any wost or better results.
   

---------------------------------------------------
it's generate an error ...
this query is the best for performance ( 0.812091 ) but it's slow against
MySQL performance ...
SELECT city_ext.city as MyCity,
number_ext.mynum as MyNumber
FROM city_ext, number_ext
WHERE number_ext.mydate = ( SELECT id FROM date_ext WHERE date_ext.mydate =
( SELECT MAX( date_ext.mydate ) FROM date_ext ) )
AND number_ext.city = city_ext.id
ORDER BY city_ext.city, number_ext.position;
However I've tried to leave mydate search and compare just city_ext and
number_ext .... these two table are very slow with a simple match of one id
.
Any new ideas ?
Thank you, andr3a
 

Are there indexes on the id fields?
John LeSueur