On 10/30/15, aa <woshishaoda07 at 163.com> wrote: > Hello, > I am a sqlite user from asia. I use sqlite for more than a year , now > the version i am using is 3.8.11.1. > But recently I meet a problem about inserting data into table.It is > too slow whit index. > The first I create a table like this: > CREATE TABLE mac_tb (mac BIGINT PRIMARY KEY? > If I insert into mac_tb with mac ordey by num desc or asc , then the > speed is fast. > If I insert the mac randomly, then the speed will grow slower whit > the increasing of data. It become slower if the count of mac_tb > more than 2,000,000. > But if i create another table without index, than the speed is so > fast. > I don't know why the performance difference is so great.
When you insert into an unindexed table, new content can be appended to the end of the table. This is very fast. And to promotes locality of reference - all the writes are to the same region of the file - namely the end of the file. When the table is indexed, however, new entries have to be added to the index so that they are all in sorted order. This means writes have to occur in random places all over the file. This dramatically reduces locality of reference, resulting in a large slowdown. You can work around the problem by inserting content into the table before creating the index. Then run CREATE INDEX to create the index when you are done. SQLite uses b-tree storage. The b-tree data structure is great at most things, but it suffers from poor locality of reference when doing random inserts (as described above). Some database engines use LSM trees for indexes. LSM trees are faster than b-trees for doing many random inserts. But they are slower than b-trees for just about everything else. Even so, LSM trees are much faster at inserts, and the slowdown for doing other things is comparitively minor. So many systems do use LSM trees. But SQLite is not (currently) one of them. -- D. Richard Hipp drh at sqlite.org
