163 wrote:
> I met a performance issue when using SQLite 3.8.4.3. I found it would
> be quite slow trying to select count(*) using a where statement with
> several OR condition. For example:
>
> select ctrl_no from T_CTRL_CLRRULE where CTRL_NO='%s' and ((PRODUCT_ID='%s'
> and OPERATE_TYPE='%s') or (PRODUCT_ID='AAAAAA' and OPERATE_TYPE='%s') or
> (PRODUCT_ID='%s' and OPERATE_TYPE='AAAAAA')) and CTRL_FLAG='%s'
This query does not select count(*).
Anyway, its EXPLAIN QUERY PLAN output is:
0|0|0|SEARCH TABLE T_CTRL_CLRRULE USING INDEX I_T_CTRL_CLRRULE1 (CTRL_NO=?
AND CTRL_FLAG=?)
> it will take 1.24ms to get the count result using above sql.
> Then I split above sql to 3 seperate sql and test the efficiency again.
>
> 1. select count(*) from T_CTRL_CLRRULE where CTRL_NO='%s' and
> PRODUCT_ID='%s' and OPERATE_TYPE='%s' and CTRL_FLAG='%s'
> 2. select count(*) from T_CTRL_CLRRULE where CTRL_NO='%s' and
> PRODUCT_ID='AAAAAA' and OPERATE_TYPE='%s' and CTRL_FLAG='%s'
> 3. select count(*) from T_CTRL_CLRRULE where CTRL_NO='%s' and
> PRODUCT_ID='%s' and OPERATE_TYPE='AAAAAA' and CTRL_FLAG='%s'
The EXPLAIN QUERY PLAN output is the same for all three:
0|0|0|SEARCH TABLE T_CTRL_CLRRULE USING COVERING INDEX UIDX_T_CTRL_CLRRULE
(CTRL_NO=? AND PRODUCT_ID=? AND OPERATE_TYPE=? AND CTRL_FLAG=?)
> I find it will only take 0.27ms on average executing all above 3 sql.
The combined OR conditions are too complex for the query optimizer, so
it does index lookup only on the other columns.
> should I split the where statement every time manually if I met above
> requirement?
You could combine the three queries like this:
select (select count(*) ...) + (select count(*) ...) + (select count(*) ...)
But better try this instead:
select count(*)
from T_CTRL_CLRRULE
where CTRL_NO='%s'
and PRODUCT_ID in ('%s', 'AAAAAA')
and OPERATE_TYPE in ('%s', 'AAAAAA')
and (PRODUCT_ID != 'AAAAAA' or OPERATE_TYPE != 'AAAAAA')
and CTRL_FLAG='%s'
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
