On 2014/03/27 03:37, Pavel Vazharov wrote:
Hi guys,
I tried the thing that you proposed.
The EXPLAIN QUERY PLAN showed
selectid order from detail
0 0 0 SEARCH TABLE event AS e USING INDEX IDX_event_1 (deleted=?
AND major=? AND rowid>?)
as you expected.
But the timing is still as in the old verions (two tests):
2014-Mar-26 17:57:13.289945:NORM: AGENT REV: 330557:330556. TIME FOR 1000
ITERATIONS: 40182 milliseconds
2014-Mar-26 18:02:16.425186:NORM: AGENT REV: 330557:330556. TIME FOR 1000
ITERATIONS: 40663 milliseconds
If I run ANALYZE with the tool and then run the same code with the new db. It
returns results like:
2014-Mar-26 18:03:32.115915:NORM: AGENT REV: 330557:330556. TIME FOR 1000
ITERATIONS: 234 milliseconds
2014-Mar-26 18:03:47.483895:NORM: AGENT REV: 330557:330556. TIME FOR 1000
ITERATIONS: 226 milliseconds
The returned table values with the second (ANALYZED) version seems correct. I'm
wondering why is this big difference?
Do you think that the second results are impossibly fast (i.e. something is
wrong) and it is related to the different versions of the SQLite tool and the
SQLite used in the application?
I'm about to build the application with the latest SQLite version and see what
will happen.
I wish I could tell you exactly, but I can't say anything more than the older query planner must have decided on a different route
from the start, and once analyzed it knows better how to get the best query performance. There were a few cases in which it would
make some decisions that seemed obviously wrong, but that's the nature of algorithms, and for that reason the new query planner aka
NGQP was implemented, a spectacular achievement in SQLite if I may say. I do not think you are doing anything wrong by the way, if
the indices work those times seem like the sort that should be achievable compared to the times in non-used index mode. Analyze is a
really important bit of the puzzle, and you can simply run it on occasion in the end-product too. (It might be a bit slow, so choose
the timing well).
We have plans updating our libraries to the recent versions, and we'll update
the SQLite too.
Best idea.
By the way, this db schema has existed long before I started my job at the
company. So, I'm not the guy who created the schema I'm just the guy who is
wondering why the query is slow.
Yeah, but when that happens, best is to start at the start and fix the schema. Upgrading it will be easy too since any version of
sqlite can read older version files, so you can just make a new better table in your code, populate it from an old table, drop the
old table and rename the new table, et voila, DB is upgraded. (Be sure to do that all in a transaction of course).
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
