hello all, first ,forgive me for freshman in sqlite.
in my project i creat table like this:
rc = sqlite3_exec(db, "CREATE TABLE " EIT_SCHEDULE " ( "
SERVICE_TYPE" INTEGER,"
CHANNEL_NAME " ,"
TABLE_ID_DB " INTEGER,"
SECTION_NUMBER " INTEGER,"
VERSION_NUMBER " INTEGER,"
SERVICE_ID " INTEGER,"
TS_ID " INTEGER,"
ON_ID " INTEGER,"
CRC_32 " INTEGER,"
EVENT_ID " INTEGER,"
START_TIME_UTC_M " INTEGER,"
START_TIME_UTC_L " INTEGER,"
DURATION_3 " INTEGER,"
CONTENT_NIBBLE_LEVEL " INTEGER,"
PARRENT_RATE_COUNTRY_CODE_3 " INTEGER,"
PARRENT_RATE_RATE " INTEGER,"
TSE_DES_REFERENCE_SERVICE_ID " INTEGER,"
TSE_DES_REFERENCE_EVENT_ID " INTEGER,"
ISO_639_LANGUAGE_CODE_SHORT_3 " INTEGER,"
NAME_LENGTH " INTEGER,"
EVENT_NAME " ,"
SHORT_LENGTH " INTEGER,"
SHORT_EVENT " ,"
DESCRIPTOR_NUMBER " INTEGER,"
LAST_DESCRIPTOR_NUMBER " INTEGER,"
ISO_639_LANGUAGE_CODE_EXTEND_3 " INTEGER,"
LENTH_OF_ITEM " INTEGER,"
ITEM " ,"
TEXT_LENGTH " INTEGER,"
TEXT_EVENT " ,"
END_TIME_UTC_M " INTEGER,"
END_TIME_UTC_L " INTEGER"
");", NULL, NULL, NULL);
then i creat two index like this:
if(sqlite3_exec(db, "CREATE INDEX schedule_nibble_index ON
"EIT_SCHEDULE"("CONTENT_NIBBLE_LEVEL","SERVICE_TYPE","START_TIME_UTC_M","START_TIME_UTC_L
","END_TIME_UTC_M","END_TIME_UTC_L");",
NULL, NULL, NULL)!=SQLITE_OK)
printf( "CREATE INDEX schedule_index ON EIT_SCHEDULE error 2 \n");
if(sqlite3_exec(db, "CREATE INDEX schedule_index ON
"EIT_SCHEDULE"("SERVICE_ID","TS_ID
","ON_ID","START_TIME_UTC_M","START_TIME_UTC_L
");",
NULL, NULL, NULL)!=SQLITE_OK)
printf( "CREATE INDEX schedule_index ON EIT_SCHEDULE error 1 \n");
then i insert about 40,000 row into table.
i use two select statement two query the row
(1)rc = sqlite3_prepare_v2(db,"SELECT * FROM " EIT_SCHEDULE" WHERE "
SERVICE_ID"=? AND "TS_ID"=? AND "ON_ID
"=? AND "START_TIME_UTC_M "=? AND "START_TIME_UTC_L ">=? ORDER BY
"START_TIME_UTC_L" LIMIT ?,1;",-1, &stmt_select_schedule, NULL);
(2)"SELECT * FROM ( SELECT * FROM ( SELECT * FROM " EIT_SCHEDULE" WHERE "
CONTENT_NIBBLE_LEVEL
"=? AND "SERVICE_TYPE"=? AND " START_TIME_UTC_M "=? AND "START_TIME_UTC_L
">=? UNION SELECT * FROM "EIT_SCHEDULE" WHERE " CONTENT_NIBBLE_LEVEL
"=? AND "SERVICE_TYPE"=? AND " END_TIME_UTC_M" =? AND "END_TIME_UTC_L" >
?) LIMIT ?, 50) ORDER BY "SERVICE_ID ","START_TIME_UTC_L" LIMIT ?,5 ;",-1,
&stmt_select_content_step, NULL);
by now the forst query is work well, but the second query is not,the second
query take about 5 times longer than the first one.i want to konw why?
i think may be the engine haven't use the index ,so i would prefer to ues
EXPLAIN syntax,but i don't know how to use it in C API and how to get the
EXPLAIN result in C API.
would anyone give me some idear about this,thans a lot.
