I observed a strange behavior. I was operating on a big table, there are 200,000 records in it. The table has a primary key or unique index, (time, id1, id2), all of these indexed columns are integers.
The following query statement executed very slow, it took 15 secs on my ARM device, 1. select max(time) from mytable where time < 99999999 and id1 = k1 and id2 = n. where 99999999 is a value that large enough that no a record has its time field equals to it. k1 is a not existed value that cannot be matched by any id1 column in the table, while n is a normal value that can be matched in the table by a subset of records. However, if I replace k1with another value that can be found in the table and keep everything unchanged, like below, 2. select max(time) from mytable where where time < 999999999 and id1 = k2 and id2 = n. This query run very well, it only took less than 1 second on the same system. Also, I tried to replace the very large time value to a small enough one, 3. select max(time) from mytable where time < 0 and id1=k1 and id2=n This query also run very fast. Could anyone explain this to me? Thanks in advance. -Woody Wu _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
