Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes
Thanks for the suggestion. Changing the USING to ON makes absolutely no difference. The speed is the same and the query plans (both EXPLAIN and EXPLAIN QUERY PLAN) are absolutely identical. Same for if I convert it to WHERE: WHERE joining_table.data_id = data_table.data_id; On 2019-12-03 14:46, Simon Slavin wrote: On 3 Dec 2019, at 8:48am, Jonathan Moules wrote: SELECT count(1) FROM data_table JOIN joining_table USING (data_id); SELECT count(1) FROM data_table JOIN joining_table ON joining_table.data_id = data_table.data_id; Given the rest of the structure you gave, including the indexes, compare the speeds of these two. Simon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes
On 3 Dec 2019, at 8:48am, Jonathan Moules wrote: > SELECT > count(1) > FROM > data_table > JOIN joining_table USING (data_id); SELECT count(1) FROM data_table JOIN joining_table ON joining_table.data_id = data_table.data_id; Given the rest of the structure you gave, including the indexes, compare the speeds of these two. Simon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes
ised me. I've seen that there are some optimisations assuming positive integers - http://peterhansen.ca/blog/sqlite-negative-integer-primary-keys.html - but it's odd that the HDD was better than the SSD for the most part with these. Also the full-size 64bit integers were a fair percentage slower than the regular integers even though there were the exact same number of them. Thanks again, Jonathan On 2019-11-26 14:40, David Raymond wrote: Not the reason for the slowdown, but note that both of these are redundant: CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table ( data_id ); CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me ( ignored_id ); ...because you declared them as the primary keys in the table creation. So you now have 2 different indexes on the exact same data for each of those. The rest of it looks fine to me anyway, and I'm not sure why you'd be seeing such slow times. Old slow hard disk? If you analyze and vacuum it does it get any better? I think the CLI has something like ".scanstats on" to get a little more info, but I'm not sure how much more info it'll provide. -Original Message----- From: sqlite-users On Behalf Of Hick Gunter Sent: Tuesday, November 26, 2019 4:57 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes You are using text columns as primary keys and referencing them directly in foreign keys. This is probably not what you want, because it duplicates the text key. Also, with foreign keys enabled, your join is not accomplishing anything more than a direct select from joining_table, just with more effort (and circumventing the count() optimization). SQLite uses an 64bit Integer as a rowid that uniquely identifies the row in the table. This is what you should be using as a foreign key, because it is twice as fast as using an index. OTOH, SQLite supports WITHOUT ROWID tables, you might like to read up on those too -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jonathan Moules Gesendet: Dienstag, 26. November 2019 10:25 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] Slow joining of tables with indexes Hi List, I have a relational table setup where I've built indexes but I'm still seeing very slow join times on middling amounts of data. I'm guessing I'm doing something wrong but I can't see what. (SQLite: 3.24.0) Simplified schema as below. The ids are 16 character hex strings. I've included the ignore_me table only because it's relevant to the indexes. Note: I can *guarantee* that the data inserted into `data_table` and `ignore_me` is ordered by their respective primary keys ASC. Entries in joining_table are ordered by one of either data_id ASC or ignored_id ASC depending on creation method. --== -- 1.7 million items CREATE TABLE data_table ( data_id TEXT PRIMARY KEY NOT NULL COLLATE NOCASE, data_1 TEXT, data_2 TEXT ); -- 1.9 million items CREATE TABLE joining_table ( data_id TEXT REFERENCES data_table (data_id) NOT NULL COLLATE NOCASE, ignored_id TEXT REFERENCES ignore_me (ignored_id) NOT NULL COLLATE NOCASE, misc_col_1 TEXT, misc_col_2 TEXT ); -- ~200,000 items CREATE TABLE ignore_me ( ignored_id TEXT PRIMARY KEY NOT NULL COLLATE NOCASE ); CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table ( data_id ); CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me ( ignored_id ); -- Allow quick joining from data_table to ignore_me CREATE INDEX IF NOT EXISTS joining_table__data_ignored_id__fk_idx ON joining_table ( data_id ASC, ignored_id ASC ); -- Allow quick joining from ignore_me to data_table CREATE INDEX IF NOT EXISTS joining_table__ignored_data_id__fk_idx ON joining_table ( ignored_id ASC, data_id ASC ); -- Example data: INSERT INTO data_table (data_id) VALUES ('00196a21e8c0f9f6'); INSERT INTO data_table (data_id) VALUES ('579c57f1268c0f5c'); INSERT INTO ignore_me VALUES ('c402eb3f05d433f2'); INSERT INTO ignore_me VALUES ('d827e58953265f63'); INSERT INTO ignore_me VALUES ('ec1d2e817f55b249'); INSERT INTO joining_table (data_id, ignored_id) VALUES ('00196a21e8c0f9f6', 'c402eb3f05d433f2'); INSERT INTO joining_table (dat
Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes
Thanks for the comments. I've done some testing. Results below for those interested. * Unnecessary manual indexes on the Primary Key - good spot, I'd forgotten SQLite does that! * I was indeed using a Hard Disk but that was intentional - this is for customers and I can't know their hardware. * INTEGERs vs WITHOUT ROW_ID vs what I have now vs full-on 64 bit INTEGERs - Tested below Non-scientific Timings are below (in seconds). "HDD" = Hard drive, otherwise it's a SSD. "Indexes" means an index built on the FK. == Original structure -- original (16 character string PK/FK) - indexes -- 4.04 -- 4.6 (hdd) -- 4.1 -- 4.7 (hdd) -- 4.14 -- 5.03 (hdd) -- original (16 character string PK/FK) - no indexes -- 4.03 -- 5.9 (hdd) -- 5.1 -- 11.4 (hdd) -- 4.18 -- 9.766 (hdd) So not much speed difference with indexes between SSD and HDD. === Original structure but changing to WITHOUT ROW_ID -- original (16 character string PK/FK) - WITHOUT ROW_ID - indexes -- 3.69 -- 2.9 (hdd) -- 3.8 -- 5.2 (hdd) -- 3.74 -- 5.8 (hdd) -- original (16 character string PK/FK) - WITHOUT ROW_ID - no indexes -- 3.45 -- 3.4 (hdd) -- 3.4 -- 3.4 (hdd) -- 8.47 -- 18.4 (hdd) Curiously with the with-indexes seems to on average be slower than without indexes for this on the HDD. == Auto-incrementing INTEGER as the ID and FK -- integer_id (autoincrement INTEGER PK/FK) - indexes -- 1.3 -- 1.21 -- 6.9 (hdd) -- 1.2 -- 4.4 (hdd) -- 2.45 -- 5.2 (hdd) -- integer_id (autoincrement INTEGER PK/FK) - no indexes -- 1.3 -- 19.3 (hdd) -- 4.7 -- 9.1 (hdd) -- 5.229 -- 18.98 (hdd) no-index speeds seem to be very random. The last test I did was to convert the hex strings to their 64bit INTEGER equivalents and use those as the keys. So still using a 64bit INTEGER as the keys, they could be anything rather than low value So my keys are like: -9223326038759585676 -5012230838021194131 -3961911462337065450 3423089283580538480 9221679147258515042 ... my integer (Hex to INTEGER PK/FK - negative PKs) - index -- 2.02 -- 2.03 -- 1.9 (hdd) -- 1.9 (hdd) -- 6.1 -- 1.9 (hdd) my integer (Hex to INTEGER PK/FK - negative PKs) - no indexes -- 2.48s -- 2.42s -- 2.4 (hdd) -- 2.4 (hdd) -- 7.5 -- 20.1 (hdd) The HDD was consistently good with these full-size 64bit keys which surprised me. I've seen that there are some optimisations assuming positive integers - http://peterhansen.ca/blog/sqlite-negative-integer-primary-keys.html - but it's odd that the HDD was better than the SSD for the most part with these. Also the full-size 64bit integers were a fair percentage slower than the regular integers even though there were the exact same number of them. Thanks again, Jonathan On 2019-11-26 14:40, David Raymond wrote: Not the reason for the slowdown, but note that both of these are redundant: CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table ( data_id ); CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me ( ignored_id ); ...because you declared them as the primary keys in the table creation. So you now have 2 different indexes on the exact same data for each of those. The rest of it looks fine to me anyway, and I'm not sure why you'd be seeing such slow times. Old slow hard disk? If you analyze and vacuum it does it get any better? I think the CLI has something like ".scanstats on" to get a little more info, but I'm not sure how much more info it'll provide. -Original Message- From: sqlite-users On Behalf Of Hick Gunter Sent: Tuesday, November 26, 2019 4:57 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes You are using text columns as primary keys and referencing them directly in foreign keys. This is probably not what you want, because it duplicates the text key. Also, with foreign keys enabled, your join is not accomplishing anything more than a direct select from joining_table, just with more effort (and circumventing the count() optimization). SQLite uses an 64bit Integer as a rowid that uniquely identifies the row in the table. This is what you should be using as a foreign key, because it is twice as fast as using an index. OTOH, SQLite supports WITHOUT ROWID tables, you might like to read up on those too -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jonathan Moules Gesendet: Dienstag, 26. November 2019 10:25 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] Slow joining of tables with indexes Hi List, I have a relational table setup where I've built indexes but I'm still seeing very slow join times on middling amounts of data. I'm guessing I'm doing something wrong but I can't see what. (SQLite: 3.24.0) Simplified schema as below. The ids are 16 character hex strings. I've included the ignore_m
Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes
Not the reason for the slowdown, but note that both of these are redundant: CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table ( data_id ); CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me ( ignored_id ); ...because you declared them as the primary keys in the table creation. So you now have 2 different indexes on the exact same data for each of those. The rest of it looks fine to me anyway, and I'm not sure why you'd be seeing such slow times. Old slow hard disk? If you analyze and vacuum it does it get any better? I think the CLI has something like ".scanstats on" to get a little more info, but I'm not sure how much more info it'll provide. -Original Message- From: sqlite-users On Behalf Of Hick Gunter Sent: Tuesday, November 26, 2019 4:57 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes You are using text columns as primary keys and referencing them directly in foreign keys. This is probably not what you want, because it duplicates the text key. Also, with foreign keys enabled, your join is not accomplishing anything more than a direct select from joining_table, just with more effort (and circumventing the count() optimization). SQLite uses an 64bit Integer as a rowid that uniquely identifies the row in the table. This is what you should be using as a foreign key, because it is twice as fast as using an index. OTOH, SQLite supports WITHOUT ROWID tables, you might like to read up on those too -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jonathan Moules Gesendet: Dienstag, 26. November 2019 10:25 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] Slow joining of tables with indexes Hi List, I have a relational table setup where I've built indexes but I'm still seeing very slow join times on middling amounts of data. I'm guessing I'm doing something wrong but I can't see what. (SQLite: 3.24.0) Simplified schema as below. The ids are 16 character hex strings. I've included the ignore_me table only because it's relevant to the indexes. Note: I can *guarantee* that the data inserted into `data_table` and `ignore_me` is ordered by their respective primary keys ASC. Entries in joining_table are ordered by one of either data_id ASC or ignored_id ASC depending on creation method. --== -- 1.7 million items CREATE TABLE data_table ( data_idTEXT PRIMARY KEY NOT NULL COLLATE NOCASE, data_1TEXT, data_2 TEXT ); -- 1.9 million items CREATE TABLE joining_table ( data_id TEXT REFERENCES data_table (data_id) NOT NULL COLLATE NOCASE, ignored_id TEXTREFERENCES ignore_me (ignored_id) NOT NULL COLLATE NOCASE, misc_col_1TEXT, misc_col_2TEXT ); -- ~200,000 items CREATE TABLE ignore_me ( ignored_idTEXT PRIMARY KEY NOT NULL COLLATE NOCASE ); CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table ( data_id ); CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me ( ignored_id ); -- Allow quick joining from data_table to ignore_me CREATE INDEX IF NOT EXISTS joining_table__data_ignored_id__fk_idx ON joining_table ( data_id ASC, ignored_id ASC ); -- Allow quick joining from ignore_me to data_table CREATE INDEX IF NOT EXISTS joining_table__ignored_data_id__fk_idx ON joining_table ( ignored_id ASC, data_id ASC ); -- Example data: INSERT INTO data_table (data_id) VALUES ('00196a21e8c0f9f6'); INSERT INTO data_table (data_id) VALUES ('579c57f1268c0f5c'); INSERT INTO ignore_me VALUES ('c402eb3f05d433f2'); INSERT INTO ignore_me VALUES ('d827e58953265f63'); INSERT INTO ignore_me VALUES ('ec1d2e817f55b249'); INSERT INTO joining_table (data_id, ignored_id) VALUES ('00196a21e8c0f9f6', 'c402eb3f05d433f2'); INSERT INTO joining_table (data_id, ignored_id) VALUES ('00196a21e8c0f9f6', 'd827e58953265f63'); INSERT INTO joining_table (data_id, ignored_id) VALUES ('579c57f1268c0f5c', 'ec1d2e817f55b249'); -- Then to test the speed I'm simply doing: SELECT count(1) FROM data_table JOIN joining_table USING (data_id); --== The query plan says it's using the indexes: SCAN TABLE joining_table USING C
Re: [sqlite] [EXTERNAL] Slow joining of tables with indexes
You are using text columns as primary keys and referencing them directly in foreign keys. This is probably not what you want, because it duplicates the text key. Also, with foreign keys enabled, your join is not accomplishing anything more than a direct select from joining_table, just with more effort (and circumventing the count() optimization). SQLite uses an 64bit Integer as a rowid that uniquely identifies the row in the table. This is what you should be using as a foreign key, because it is twice as fast as using an index. OTOH, SQLite supports WITHOUT ROWID tables, you might like to read up on those too -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jonathan Moules Gesendet: Dienstag, 26. November 2019 10:25 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] Slow joining of tables with indexes Hi List, I have a relational table setup where I've built indexes but I'm still seeing very slow join times on middling amounts of data. I'm guessing I'm doing something wrong but I can't see what. (SQLite: 3.24.0) Simplified schema as below. The ids are 16 character hex strings. I've included the ignore_me table only because it's relevant to the indexes. Note: I can *guarantee* that the data inserted into `data_table` and `ignore_me` is ordered by their respective primary keys ASC. Entries in joining_table are ordered by one of either data_id ASC or ignored_id ASC depending on creation method. --== -- 1.7 million items CREATE TABLE data_table ( data_idTEXT PRIMARY KEY NOT NULL COLLATE NOCASE, data_1TEXT, data_2 TEXT ); -- 1.9 million items CREATE TABLE joining_table ( data_id TEXT REFERENCES data_table (data_id) NOT NULL COLLATE NOCASE, ignored_id TEXTREFERENCES ignore_me (ignored_id) NOT NULL COLLATE NOCASE, misc_col_1TEXT, misc_col_2TEXT ); -- ~200,000 items CREATE TABLE ignore_me ( ignored_idTEXT PRIMARY KEY NOT NULL COLLATE NOCASE ); CREATE INDEX IF NOT EXISTS data_table__data_id__pk_idx ON data_table ( data_id ); CREATE INDEX IF NOT EXISTS ignore_me__ignored_id__pk_idx ON ignore_me ( ignored_id ); -- Allow quick joining from data_table to ignore_me CREATE INDEX IF NOT EXISTS joining_table__data_ignored_id__fk_idx ON joining_table ( data_id ASC, ignored_id ASC ); -- Allow quick joining from ignore_me to data_table CREATE INDEX IF NOT EXISTS joining_table__ignored_data_id__fk_idx ON joining_table ( ignored_id ASC, data_id ASC ); -- Example data: INSERT INTO data_table (data_id) VALUES ('00196a21e8c0f9f6'); INSERT INTO data_table (data_id) VALUES ('579c57f1268c0f5c'); INSERT INTO ignore_me VALUES ('c402eb3f05d433f2'); INSERT INTO ignore_me VALUES ('d827e58953265f63'); INSERT INTO ignore_me VALUES ('ec1d2e817f55b249'); INSERT INTO joining_table (data_id, ignored_id) VALUES ('00196a21e8c0f9f6', 'c402eb3f05d433f2'); INSERT INTO joining_table (data_id, ignored_id) VALUES ('00196a21e8c0f9f6', 'd827e58953265f63'); INSERT INTO joining_table (data_id, ignored_id) VALUES ('579c57f1268c0f5c', 'ec1d2e817f55b249'); -- Then to test the speed I'm simply doing: SELECT count(1) FROM data_table JOIN joining_table USING (data_id); --== The query plan says it's using the indexes: SCAN TABLE joining_table USING COVERING INDEX joining_table__ignored_data_id__fk_idx SEARCH TABLE data_table USING COVERING INDEX data_table__data_id__pk_idx (data_id=?) But it takes about 20 seconds to do that count on the full dataset. The full EXPLAIN from the full dataset: 0Init016000 1Null01100 2OpenRead27718750k(3,NOCASE,NOCASE,)00 3OpenRead37377150k(2,NOCASE,)02 4Rewind2122000 5Column21200 6SeekGE3112100 7IdxGT3112100 8Integer13000 9AggStep0031count(1)01 10Next37100 11Next25001 12AggFinal110count(1)00 13Copy14000 14ResultRow41000 15Halt00000 16Transaction0077001 17Goto01000 Thoughts? What (probably obvious) thing am I missing? Thanks, Jonathan __