Sripathi Raj wrote:
Hi,
I have a table with 500,000 records. The following is the schema of that
table:
CREATE TABLE ES_SRC_MEDIA_INFO (GID INTEGER PRIMARY KEY AUTOINCREMENT,
MEDIAPATH VARCHAR(256) NOT NULL UNIQUE,
BYTES_USED LONG,
BYTES_ON_DISK LONG,
MTIME LONG,
CTIME LONG,
TYPE VARCHAR(20),
CATEGORY VARCHAR(20),
TIMESTAMP LONG,
JOBID VARCHAR(30)
Main question: Using DBD-SQLite, select count(gid) from es_src_media_info
takes 130 secs. What gives?
I added an unique index on GID and tried it select count(gid) from
es_src_media_info where gid >= 1. It took around 90 seconds this time. Is
there any way to speed this up other than adding a trigger?
Small question: Does sqlite take the column types and length into
consideration while creating the table?
Thanks,
Raj
With or without an index Sqlite has to access every row to get a count.
If you want a rapidly accessed count keep a running total.
Sqlite ignores the text field sizes and makes them all of type TEXT. In
stores integers as 65 bit signed integers and other numbers as 64 bit
floating point. Read up on Sqlite "manifest typing" to learn more on
how it stores data according to actual type rather than declared type.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
