On Tue, 2003-11-04 at 05:39, D. Richard Hipp wrote: > Avner Levy wrote: > > > > We have just finished testing the same scenario with MySql at amazingly > > they continued to insert 1500-3000 rows per second even when the > > database had 60,000,000 records. I don't know how this magic is done... > > Nor do I. If anybody can clue me in, I would appreciate it. I suspect > the secret must be in the file format for their indices. Does anybody > have any idea what that format is? Or how indices work in MySQL?
I think I can reproduce this, but it took me a while to make things fair: I started my tests by simply loading 30,000 records of this variety into both systems. MySQL: 1m43.661s SQLite: 0m3.198s That's when the columns are labeled VARCHAR(255) NOT NULL to MySQL; If I decide to be terribly evil, you can see CHAR(255) makes everything much worse: MySQL: 2m9.326s Interestingly enough, if I give MySQL a bone, and call them INTEGER NOT NULL: MySQL: 0m13.327s Which sounds a whole hell of a lot more like the scale I'm seeing described. SQLite still wins though... Now my next tests involved writing 100,000 records, doing a filesystem sync and waiting for the drive to drop, then burning in 30,000 records: SQLite: 11m37.869s MySQL: 0m47.218s This looks like I've got it reproduced after all, doesn't it? Except that wasn't fair- I used INTEGER NOT NULL that time. MySQL: 12m57.978s (VARCHAR) MySQL: 12m13.078s (CHAR) So for a 3x increase in data, SQLite takes 30x longer, and MySQL appears to scale linearly until we actually make it work as hard as SQLite, in which case it only takes 10x longer- and when we try and cheat MySQL again with CHAR(255), we see almost the same values. Sound more fair? 10x versus 30x? Now, then. It should be quite obvious that what SQLite loses is twofold; 1. SQLite cannot give INTEGERs any special consideration (because we're typeless) so therefore traversing variable-length keys costs us quite a bit. 2. MySQL is apparently packing several index values into the same block- something that might be worth looking into. #1 appears to allow MySQL to shortcut SQLite in a small number of cases- actually, that's conjecture. I don't know how often it is that we build a table with multiple INTEGER indexes-- it doesn't happen often enough to me, but oh well... let's say small number of scenarios then. ... but #2- which has less of an effect overall allows MySQL to change 30x into 10x- something almost worth optimizing... I think this supports the idea that a re-engineering of the indexing system is definitely a good idea. NOTE that I was never able to achieve quite the same values as reported- interesting ones all the same, but what's curious is that MySQL writes to separate files for table data. Having 60,000,000 records in one table simply isn't going to affect MySQL's inserts against another table. It's a worthless test, IMO-- this is what the ATTACH keywords are for :) --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
