The difference is that #2 mentions only one field from ItemsME, namely IDR. The value of that field comes from the index, the table itself doesn't need to be read at all. It's not even clear why #2 bothers to join with ItemsME at all - it's a no-op. #1 uses more fields from ItemsME, so it needs to actually look up and read records from that table.
However fact is that IDR is non-existant - for that specific query (which SQLite should see from index) so it should not touch table - just fill with NULLs. The more important thing is - even if I add [ItemsME].[insert whatever integer field here] into result fields it's same speed as in case 2) - that is relatively fast. Just touching any text/blob fields in query make it run ~12x slower. And considering resultset containing few rows even if all texts were split over 2 pages (none is longer than 300 chars) this would equal to records * 2kB IO. in case of 50 records that's 100kB. Do you really think SQLite needs 1.8 seconds for 100kB in reads on SSD drive ? I certainly hope not. Maybe there is reason RIGHT JOIN isn't implemented ? BTW even the 2nd case is considerably slower than single table selects. the TZB_MATCHRECURSIVE is doing way more difficult job (selecting all childs and filtering them) but only in single table and is still faster (around 40ms per top level parent folder which usually contain ~700 folders/items). Just out of curiosity is it possible to download some older versions of SQLite to test if this behaviour is present in it ? I can only find current version on download page. nOOb here. _______________________________________________ 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
