Simon Slavin-2 wrote:
>
>
> On 21 Aug 2009, at 3:26am, pierr wrote:
>
>> I did not know the sequence in defining the field matters. This is
>> what I should have done.
>
> Sorry, I should have explained better. You were right: there is no
> difference. I was just rearranging the fields in the classic way:
> with the primary key column as the first column. It helps me think
> about how the database works. You did nothing wrong.
>
Hi Simon,
It do make a difference.
With this schema,
CREATE TABLE IF NOT EXISTS tblIndex(
frame_type INTEGER,
pts VARCHAR(5)
ts_start INTEGER PRIMARY KEY,
ts_end INTEGER,
)
There will be a rowid field in the database ; and there is a
sqlite_autoindex_tblIndex_1 build on ts_start automatically ; so 1,800,000
records (16bytes each) takes 62M .
With your schema , which is much better
CREATE TABLE IF NOT EXISTS tblIndex(
ts_start INTEGER PRIMARY KEY,
ts_end INTEGER,
frame_type INTEGER,
pts VARCHAR(5)
)
There will be NO rowid field in the database ;and 1,800,000 records
(16bytes each) takes only 35M.
>>> Whichever one of these is at fault, a delete command selecting on an
>>> indexed column and deleting 90000 records from a five column table
>>> should not take 17 seconds.
>>
>> I am sorry, I should have mentioned It (17 seconds to delete 90000)
>> was
>> tested on a 350M MIPS CPU. And after changing to the schema you
>> suggested,
>> it still take 17 seconds to delete 90000 records.
>> On my 1.8G Hz Ubuntu desktop it tooks 800ms. So is this delete
>> performance
>> is the limit we can achieve? Any other options I can improve this?
>
>
>> BTW: I used following option to build the libarary. Is there any
>> thing I
>> can expore here? (-O2 and -Os seem has no big difference on
>> performance.)
>> mips24k-linux-gcc -Os -fPIC -c *.c
>> mips24k-linux-gcc -shared -o mips_libsqlite3.so.1 sqlite3.o
>
>
> Putting these together, your 350 MIPS CPU machine is a MIPS 24K
> machine. There are various things to consider: not only CPU speed but
> also memory bandwidth, memory speed, hard disk throughput, and other
> things I have no idea about. And I have no idea what part threading
> would pay on such a platform. Sorry but I have no experience with
> these. Perhaps someone else here does.
>
> Simon.
>
Thanks for your insight,Simon.
--
View this message in context:
http://www.nabble.com/suggestion-on-the-database-design-tp25062134p25073602.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
