Re: [sqlite] RE: SQLite performance with mid-size databases
On 2004-06-17, at 16.39, [EMAIL PROTECTED] wrote: If you have a large record size, then it also helps tremendously to put the indexed columns first in the row (I had a table with large blob data that was terribly slow to query until I moved the small, non-blob columns first). [That was found out by trial and error] Really!? Let's see if I understand you correctly: Your SELECT statement is exactly the same, but you just changed the order of the columns in one table, and all of a sudden your SELECT query performed better? I do believe you if you say so, but I'm just very curious as to why? If not too much trouble, would you please post the query and the table schema? thanks /jak - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite performance with mid-size databases
On 2004-06-16, at 06.04, Richard Kuo wrote: Empirically speaking, we display our data in a scrolling 2 dimensional grid format. With MS access, this grid responds instantaneously when moving through the grid. With SQLite, there is very noticable stalling and lag and the disk i/o is higher than MS Access by roughly a factor of 10. Hello Richard, I too hit a rather puzzling SELECT performance problem last year. Apparently, if you have a table with many records, and you perform a SELECT on an indexed column where most of the values are the same, performance goes way down. The mail correspondence from January 2003 does not seem to be in the archive, so below I have pasted a very enlightening response from DRH to my question. Hope this helps. /jak On 2003-01-27, at 00.00, D. Richard Hipp wrote: Example: CREATE TABLE t1(a,b,c); CREATE INDEX i1a ON t1(a); CREATE INDEX i1b ON t1(b); Next you insert lots of data where the value for T1.B is usually the same, say 'xyzzy'. Then you do a query: SELECT * FROM t1 WHERE a=5 AND b='xyzzy'; When it is compiling this query, the optimizer can choose to use either index I1A and the "a=5" expression or it can choose to use index I1B and the "b='xyzzy'" expression. The choice it makes is arbitrary. But I1B would be the wrong choice because almost every entry in T1 is a match for "b='xyzzy'". So the query must read in every one of these entries and check each one to see if "a=5". This can take even longer than doing a full table scan. I1A is the right index to use here because only a few entries of T1 will match "a=5". So only a few entries have to be read in and checked for "b='xyzzy'" and the query goes MUCH faster. Enterprise scale database engines do a more sophisticated job of selecting indices (when there is a choice like this) by collecting lots of statistics on the indices and using complex algorithms to make the choice. SQLite takes the easy way out and makes an arbitrary choice. With SQLite, it is up to you, the query author, to select an appropriate index when the choice of indices might make a difference. You can disable the other index by modifying the WHERE clause. To disable the I1A index, rewrite the WHERE clause like this: SELECT * FROM t1 WHERE a+0=5 AND b='xyzzy'; To disable the I1B index you could write SELECT * FROM t1 WHERE a=5 AND b LIKE 'xyzzy'; I should probably write some documentation talking about this and put it on the website - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Dropping an old index
I tested some more, and apparently: In SQLite 2.7.5, the command CREATE INDEX 'addressesZipIndex' ON 'addresses' (zip) will create an index named addressesZipIndex (WITHOUT the single quotes.) The command DROP INDEX addressesZipIndex (WITHOUT single quotes) will work as expected when running SQLite 2.7.5. The command DROP INDEX addressesZipIndex (WITHOUT single quotes) will NOT work when running SQLite 2.8.13, although curiously... ...the command DROP INDEX 'addressesZipIndex' (WITH single quotes) will work running SQLite 2.8.13. Spooky... In SQLite 2.8.13, the same command CREATE INDEX 'addressesZipIndex' ON 'addresses' (zip) will create an index named 'addressesZipIndex' (WITH single quotes.) The command DROP INDEX 'addressesZipIndex' (WITH single quotes) will work running SQLite 2.8.13. The command DROP INDEX addressesZipIndex (WITHOUT single quotes) will NOT work running SQLite 2.8.13. In SQLite 2.8.13, the command CREATE INDEX addressesZipIndex ON addresses (zip) will create an index named addressesZipIndex (WITHOUT single quotes, as expected). The command DROP INDEX 'addressesZipIndex' (WITH single quotes) will NOT work running SQLite 2.8.13. The command DROP INDEX addressesZipIndex (WITHOUT single quotes) WILL work running SQLite 2.8.13, as expected. So, sure, one could try to DROP with and without quotes and see which works. But is there an 'official' story here? Shouldn't CREATE INDEX ignore any quotes? And shouldn't both variants of DROP work, regardless? /jak, sleepless in sweden (3.25 AM) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Dropping an old index
On 2004-03-30, at 19.11, Will Leshner wrote: On Mar 30, 2004, at 9:04 AM, Jacob Engstrand wrote: For example, I created an index using the following in 2.7.5: CREATE INDEX 'addressesZipIndex' ON 'addresses' (zip) Hmm. This might be a bug, actually, but if you wrapped your index name in single quotes like that, then the quotes became part of the name. I just created one like that and now I can't delete it. I think the names of indexes probably shouldn't be wrapped in quotes when they are created. Yes, I thought about that, but the quotes are not part of the name in the SQLITE_MASTER table. Of course, I did try to "DROP INDEX 'addressesZipIndex'" too, but no success... :( /jak - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Dropping an old index
Hi All, I've used SQLite 2.7.5 in my project for a long time, so I'm upgrading to the latest (2.8.13). But now SQLite returns error code 1 when I try to drop the indices I created in version 2.7.5. For example, I created an index using the following in 2.7.5: CREATE INDEX 'addressesZipIndex' ON 'addresses' (zip) Now I open the same database file with SQLite 2.8.13, and I want to drop that index: DROP INDEX addressesZipIndex ... which returns error code 1. The index is definitely there - looking it up in SQLITE_MASTER table confirms that. The same DROP command (and the very same C code) worked perfectly well with SQLite 2.7.5... Any ideas at all? Thanks! /jak (still thinking that SQLite is awesome :) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
