Hi Pavel,
You wrote:
*******************************************************************************************************************
A) What version of SQLite are you using ?
- We are using SQLite version 3.6.17
- We are using SQLite through the C++ SOCI library - 3.1.0
and then:
Also, I'm running the ANALYZE, EXPLAIN and EXPLAIN QUERY PLAN commands through
an external tool which uses SQLite.dll version 3.8.2.
My good man, do you have any idea the kind of trouble you are courting? Analyze, if used in the way you do, writes to the actual
database (which is how the QP and NGQP knows how to best plan queries). In your case it does so using SQLite version 3.8.2 while you
have the other app you are making write to the same DB using SQLite 3.6.17... I'm surprised you have not corrupted the DB yet,
probably since you are not using any partial indices or without rowid tables etc, but it is an accident waiting to happen.
Also, why all the Double-quotes around your column names in the create statement? I'm sure it will work but it isn't the right SQL
compatible method, either use SQL backquotes or no quotes. Single and double quotes (preferably single quotes) in general should
only indicate string values, not object names.
You seem to employ almost pedantic use of quotes in one section, and then nothing in the next... I can only assume the inconsistency
is due to the other tools you use.
Also, your PRIMARY KEY id is an alias for the rowid, not sure if you can force autoincrement on it then, but you certainly do not
need to specify NOT NULL for it. Then there are transparent and major, why do they have both NOT NULL and DEFAULT set? If you have
DEFAULT it is verily impossible to populate a null value in that field, even if you specifically add it as NULL in your insert
statement, it will just get the default value.
Kindly humour us and try this schema in your DB, copy your data to it and run the tests - I cannot do it myself since I don't have a
3.6.17 version, nor any of your data, but it works in 3.8.3 with it's NGQP very well with fake data. (Not that I expected anything
else...)
CREATE TABLE `event` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`type` INTEGER NOT NULL,
`current` INTEGER DEFAULT 1,
`timestamp` INTEGER NOT NULL,
`file_id` INTEGER,
`folder_id` INTEGER,
`deleted` INTEGER NOT NULL,
`previous_name` TEXT,
`previous_folder_id` INTEGER,
`rsub_id` INTEGER,
`person_id` INTEGER,
`guest_id` INTEGER,
`transparent` INTEGER DEFAULT 0,
`major` INTEGER DEFAULT 0
);
CREATE INDEX `IDX_event_1` ON `event` (`deleted`, `major`);
CREATE TRIGGER `update_event` AFTER UPDATE ON `event`
FOR EACH ROW WHEN (old.type != new.type)
BEGIN
UPDATE `event` SET `major` = NEW.`type` IN (3, 4, 5, 6, 7, 8, 9) WHERE `id` =
NEW.`id`;
END;
CREATE TRIGGER `insert_event` AFTER INSERT ON `event`
BEGIN
UPDATE `event` SET `major` = NEW.`type` IN (3, 4, 5, 6, 7, 8, 9) WHERE `id` =
NEW.`id`;
END;
CREATE INDEX `IDX_event_2` ON `event` (`file_id`, `deleted`);
Good luck!
PS: No those quotes are not all needed, but that is the preferable way, and everything needn't be in CAPS, it's just easier that way
to see which words belong to SQL and which to your objects... a primitive type of code-highlighting if you will.
On 2014/03/26 20:30, Pavel Vazharov wrote:
Hi guys,
I tried the proposed thing to change the index to this:
CREATE INDEX IDX_event_1 ON event (deleted, major);
Now EXPLAIN QUERY PLAN returns:
explain query plan select e.type, e.id, e.rsub_id, e.person_id, e.timestamp,
e.file_id, e.previous_name, e.previous_folder_id, e.transparent, e.folder_id from
event e where e.deleted = 0 and major = 0 and e.id > 330557 order by e.id asc
limit 1
selectid order from detail
0 0 0 SEARCH TABLE event AS e USING INDEX IDX_event_1 (deleted=?
AND major=? AND rowid>?)
But the time from my test scenario is in the same range as before:
2014-Mar-26 11:11:44.456076:NORM: AGENT REV: 330557:330556. TIME FOR 1000
ITERATIONS: 39239 milliseconds
================================================================================
Several additional things that could be helpfull:
I ran ANALYZE on the original DB and the EXPLAIN QUERY PLAN returned that a
different index will be used:
selectid order from detail
0 0 0 SEARCH TABLE event AS e USING INTEGER PRIMARY KEY
(rowid>?)
I know that I mentioned the above information in my previous email, but I
forgot to mention the timing results with this db (nothing else is changed,
just the db is analyzed)
2014-Mar-26 11:24:15.954625:NORM: AGENT REV: 330557:330556. TIME FOR 1000
ITERATIONS: 234 milliseconds
If I run ANALYZE on the db with the new index INDEX IDX_event_1 ON event
(deleted, major) the EXPLAIN QUERY PLAN result remains the same and the timing
returns the same results.
Also, I'm running the ANALYZE, EXPLAIN and EXPLAIN QUERY PLAN commands through
an external tool which uses SQLite.dll version 3.8.2.
Thanks,
Pavel.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
