On Tue, 2007-03-27 at 11:53 -0600, Dennis Cote wrote: > Stephen Toney wrote: > > > > Meta-question: this is the second time I've asked this question. The > > first was about a month ago and got not a single reply. Is there > > something wrong with my postings? Or is this just not an interesting > > topic? > > > > > Stephen, > > There is nothing wrong with your question. In fact it was very clearly > stated. > > I just don't have any answers for you. I suspect that others are in the > same boat. > > It is normal for an index creation operation to take some time since it > is inserting index records into a btree in random order. It involves > many updates to pages throughout the index. > > It does seem strange that you are seeing such different times for the > two cases (single column vs compound index). Are you sure about the > times you posted? Were they indexing the exact same table? Were both the > indexes created after the fill operation during your timing tests (ie. > fill + index(single) and then fill + index(compound)) and not one after > the other (i.e. fill + index(single) + index(compound))?
Thanks, Dennis, I feel reasonably confident about my numbers, but since the system is in development, other factors may have changed. I would re-test before asking anyone else to try to replicate this. The timings were done by recreating the db content each time with no indexes, then building either the multi-column index or the two single-column indexes. > Can you supply sample data if someone wants to try some test of their > own? It wouldn't have to be the full data set you are using. We could > use a subset to get relative timings in the seconds range rather than > minutes (This assumes that you are not running into some cache size > problems that slow down the larger data set disproportionately). Yes, I'd be glad to supply sample data, but do not have an FTP site. I'm not sure how big the sample should be. Maybe I should test some samples myself before asking anyone else to. As you say, the problem may not exist at a smaller size. Another reason for my puzzlement -- although I love SQLite, my expectations are based on using Foxpro for many years. Foxpro's indexing speed for a problem like this is about 10 - 20 times faster. And I've never come across a Foxpro database where the indexing took longer than the loading -- and Foxpro is blazingly fast at loading. So I assumed that every DBMS would be faster at indexing than loading. (Both use B-trees for indexes, so I believe it's a meaningful comparison. But maybe in this case the single-file architecture of SQLite works against it; Foxpro uses a binary format for its B-trees.) In other words, since SQLite is so fast at some things, I expect it to be fast at all things. Is this unreasonable? Is it optimized for fast retrieval and not indexing? Thanks! -- Stephen Toney Systems Planning [EMAIL PROTECTED] http://www.systemsplanning.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
