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;
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.
On Tue, 2004-09-07 at 01:59, Andrea Giammarchi wrote:
> Hi All,
> I'm doing some tests with SQLite ( 2.8.14 ) against MySQL ( 4.1.3 beta )
> with these 2 tables:
>
> date_ext ( id INTEGER PRIMARY KEY, mydate DATE not null )
> city_ext ( id INTEGER PRIMARY KEY, city CHAR( 2 ) )
> number_ext ( id INTEGER PRIMARY KEY, mydate INTEGER not null, city
> INTEGER(2) not null, position INTEGER(1) not null, mynum INTEGER(2) not
> null )
>
>
> ... and this is the query:
>
> SELECT date_ext.mydate as MyDate, city_ext.city as MyCity, number_ext.mynum
> as MyNumber FROM city_ext, number_ext, date_ext WHERE number_ext.mydate =
> date_ext.id AND number_ext.city = city_ext.id AND date_ext.mydate = ( SELECT
> MAX( date_ext.mydate ) FROM date_ext ) ORDER BY city_ext.city,
> number_ext.position
>
>
> This query is done in MySQL in 0.31416 seconds
>
> In SQLite is done in 1.408461 seconds ( with the same result of MySQL
> query )
>
> Does anyone know why there's too much difference from this simple query in
> the same type of tables ???
>
> DATE is a column with 'YYYY-MM-DD' info, date_ext contains 3406 records,
> city_ext contains 10 records, number_ext contains 170300 records.
>
> Platform is Windows 2000 with Apache 1.3.31 and PHP5.0.1 as CGI with
> dedicated port :81
>
> Thank you.
> andr3a
>
>
>
>
