ansaurus

Question

SQLite always does a Full Table Scan when like used?

Answer 1

+2 A:

The LIKE operator will perform a full table scan if operand string begins with a wildcard character ("LIKE '%foo'"). It will use an index (if available) otherwise.

BipedalShark 2009-10-22 21:08:55

Answer 2

+1 A:

If your LIKE uses a wildcard at the beginning of the match expression, such as '%hris McCall', indexes will not be used.

Chris McCall 2009-10-22 21:12:03

Unfortunately, the wildcard '%' is used as a suffix - AFTER a constant text - something like 'FTP%'What is amazing is that I just finished a little bit of data analysis and it looks like most of the "pattern" strings are constants - like 'ftp://abs.com' - so there was no need to use like at all!Just for the sake of it, I wrote a one liner that calls a version of the query that uses '=' instead of 'like' when there are no wildcards, and it's wayyy faster - '=' takes 3 seconds on what takes 'like' 42 minutes!Is there no way to make 'like' call '=' if no wildcards are used?

PoorLuzer 2009-10-22 21:22:25

Why do you use SQLite for a project that has enough data to take 42 minutes? That must be hundreds of GB of data.

erikkallen 2009-10-22 22:11:39

... or maybe not hundreds, but probably tens.

erikkallen 2009-10-22 22:12:26

No! This is for db that is ~12MB.Note the difference in timings in using 'like' vs '='

PoorLuzer 2009-10-23 01:57:23

42 minutes on a 12MB database? This is a little hard to believe. Would you mind sharing the queries and table schema?

BipedalShark 2009-10-23 17:21:53

Things do not seem to be working out - I need to share the database - but the problem is that the values are extremely confidential - would sharing the schema and PERL code with embedded queries be enough?Don't you need the complete 12MB db to do timings properly?

PoorLuzer 2009-10-23 21:24:09

Answer 3

+1 A:

The SQLite Query Planner

4.0 The LIKE optimization

Terms that are composed of the LIKE or GLOB operator can sometimes be used to constrain indices. There are many conditions on this use:

The left-hand side of the LIKE or GLOB operator must be the name of an indexed column.

The right-hand side of the LIKE or GLOB must be a string literal that does not begin with a wildcard character. [...]

Update: by default case_sensitive_like mode is OFF. Turning it ON might make it behave like =.

PRAGMA case_sensitive_like = ON; -- OFF
SELECT * FROM your_table WHERE field LIKE '...'

Nick D 2009-10-22 21:15:52

the left hadn side of like is the primary key and the right is a constant string followed sometimes by a wildcard.As I said, most of the "pattern" strings are constants - like 'ftp:abs.com' - so there was no need to use like at all! Just for the sake of it, I wrote a one liner that calls a version of the query that uses '=' instead of 'like' when there are no wildcards, and it's wayyy faster - '=' takes 3 seconds on what takes 'like' 42 minutes! Is there no way to make 'like' call '=' if no wildcards are used?

PoorLuzer 2009-10-22 21:58:36

@PoorLuzer, check out my update.

Nick D 2009-10-22 22:15:41

.. I will have a look today and let you know.

PoorLuzer 2009-10-23 01:58:03

no difference in timing at all - I need to share the databse - but the problem is that the values are extremely confidential - would sharing the schema and PERL code be enough?

PoorLuzer 2009-10-23 21:23:10

ansaurus

tags:

views:

answers:

SQLite always does a Full Table Scan when like used?

related questions