0

I have a table called street_names:

CREATE TABLE street_names (
            id INTEGER PRIMARY KEY NOT NULL,
            name TEXT UNIQUE NOT NULL
          );

When searched using LIKE it uses the index and returns results instantly. However, take this larger expression:

SELECT sn.name, sa.house_number, sa.entrance, pc.postal_code, ci.name, mu.name,
       co.name, sa.latitude, sa.longitude
FROM
  street_addresses AS sa
  INNER JOIN street_names   AS sn ON sa.street_name  = sn.id
  INNER JOIN postal_codes   AS pc ON sa.postal_code  = pc.id
  INNER JOIN cities         AS ci ON sa.city         = ci.id
  INNER JOIN municipalities AS mu ON sa.municipality = mu.id
  INNER JOIN counties       AS co ON mu.county       = co.id
WHERE
  sn.name = "FORNEBUVEIEN" AND
  sa.house_number = 13
ORDER BY ci.name ASC, sn.name ASC, sa.house_number ASC, sa.entrance ASC
LIMIT 0, 100;

In its current state it's lightning fast and can run 6000 times per second on my machine, but as soon as I change the = to a LIKE on the street name:

SELECT sn.name, sa.house_number, sa.entrance, pc.postal_code, ci.name, mu.name,
       co.name, sa.latitude, sa.longitude
FROM
  street_addresses AS sa
  INNER JOIN street_names   AS sn ON sa.street_name  = sn.id
  INNER JOIN postal_codes   AS pc ON sa.postal_code  = pc.id
  INNER JOIN cities         AS ci ON sa.city         = ci.id
  INNER JOIN municipalities AS mu ON sa.municipality = mu.id
  INNER JOIN counties       AS co ON mu.county       = co.id
WHERE
  sn.name LIKE "FORNEBUVEIEN" AND
  sa.house_number = 13
ORDER BY ci.name ASC, sn.name ASC, sa.house_number ASC, sa.entrance ASC
LIMIT 0, 100;

It turns sour and runs perhaps 10 times per second on my machine. Why is this? The only change I made was changing an = to a LIKE on an indexed column, and the query didn't even include any wildcards.

Table schemas:

CREATE TABLE street_addresses (
            id INTEGER PRIMARY KEY NOT NULL,
            house_number INTEGER NOT NULL,
            entrance TEXT NOT NULL,
            latitude REAL NOT NULL,
            longitude REAL NOT NULL,
            street_name INTEGER NOT NULL REFERENCES street_names(id),
            postal_code INTEGER NOT NULL REFERENCES postal_codes(id),
            city INTEGER NOT NULL REFERENCES cities(id),
            municipality INTEGER NOT NULL REFERENCES municipalities(id),
            CONSTRAINT unique_address UNIQUE(
              street_name, house_number, entrance, postal_code, city
            )
          );

CREATE TABLE street_names (
            id INTEGER PRIMARY KEY NOT NULL,
            name TEXT UNIQUE NOT NULL
          );

CREATE TABLE postal_codes (
            id INTEGER PRIMARY KEY NOT NULL,
            postal_code INTEGER NOT NULL,
            city INTEGER NOT NULL REFERENCES cities(id),
            CONSTRAINT unique_postal_code UNIQUE(postal_code, city)
          );

CREATE TABLE cities (
            id INTEGER PRIMARY KEY NOT NULL,
            name TEXT NOT NULL,
            municipality INTEGER NOT NULL REFERENCES municipalities(id),
            CONSTRAINT unique_city UNIQUE(name, municipality)
          );

CREATE TABLE municipalities (
            id INTEGER PRIMARY KEY NOT NULL,
            name TEXT NOT NULL,
            NUMBER INTEGER UNIQUE NOT NULL,
            county INTEGER NOT NULL REFERENCES counties(id),
            CONSTRAINT unique_municipality UNIQUE(name, county)
          );

CREATE TABLE counties (
            id INTEGER PRIMARY KEY NOT NULL,
            name TEXT UNIQUE NOT NULL
          );

EXPLAIN for query ... sn.name = ... :

sqlite> EXPLAIN SELECT sn.name, sa.house_number, sa.entrance, pc.postal_code, ci.name, mu.name, co.name, sa.latitude, sa.longitude FROM street_addresses AS sa INNER JOIN street_names   AS sn ON sa.street_name  = sn.id INNER JOIN postal_codes   AS pc ON sa.postal_code  = pc.id INNER JOIN cities         AS ci ON sa.city         = ci.id INNER JOIN municipalities AS mu ON sa.municipality = mu.id INNER JOIN counties       AS co ON mu.county       = co.id WHERE sn.name = "FORNEBUVEIEN" AND sa.house_number = 13 ORDER BY ci.name ASC, sn.name ASC, sa.house_number ASC, sa.entrance ASC LIMIT 0, 100; 
addr        opcode      p1          p2          p3          p4          p5          comment   
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
0           Init        0           91          0                       00                    
1           OpenEpheme  6           6           0           k(4,B,B,B,  00                    
2           Integer     100         1           0                       00                    
3           Integer     0           2           0                       00                    
4           MustBeInt   2           0           0                       00                    
5           IfPos       2           7           0                       00                    
6           Integer     0           2           0                       00                    
7           Add         1           2           3                       00                    
8           IfPos       1           10          0                       00                    
9           Integer     -1          3           0                       00                    
10          OpenRead    7           12          0           k(2,nil,ni  00                    
11          OpenRead    0           13          0           9           00                    
12          OpenRead    8           14          0           k(5,nil,ni  00                    
13          OpenRead    2           9           0           2           00                    
14          OpenRead    3           7           0           2           00                    
15          OpenRead    4           4           0           4           00                    
16          OpenRead    5           2           0           2           00                    
17          String8     0           4           0           FORNEBUVEI  00                    
18          SeekGE      7           65          4           1           00                    
19          IdxGT       7           65          4           1           00                    
20          IdxRowid    7           5           0                       00                    
21          IsNull      5           65          0                       00                    
22          Integer     13          6           0                       00                    
23          SeekGE      8           65          5           2           00                    
24          IdxGT       8           65          5           2           00                    
25          IdxRowid    8           7           0                       00                    
26          Seek        0           7           0                       00                    
27          Column      8           3           8                       00                    
28          MustBeInt   8           64          0                       00                    
29          NotExists   2           64          8                       00                    
30          Column      8           4           9                       00                    
31          MustBeInt   9           64          0                       00                    
32          NotExists   3           64          9                       00                    
33          Column      0           8           10                      00                    
34          MustBeInt   10          64          0                       00                    
35          NotExists   4           64          10                      00                    
36          Column      4           3           11                      00                    
37          MustBeInt   11          64          0                       00                    
38          NotExists   5           64          11                      00                    
39          Column      7           0           12                      00                    
40          Column      8           1           13                      00                    
41          Column      8           2           14                      00                    
42          Column      2           1           15                      00                    
43          Column      3           1           16                      00                    
44          Column      4           1           17                      00                    
45          Column      5           1           18                      00                    
46          Column      0           3           19                      00                    
47          RealAffini  19          0           0                       00                    
48          Column      0           4           20                      00                    
49          RealAffini  20          0           0                       00                    
50          MakeRecord  12          9           21                      00                    
51          Column      3           1           22                      00                    
52          Column      7           0           23                      00                    
53          Column      8           1           24                      00                    
54          Column      8           2           25                      00                    
55          Sequence    6           26          0                       00                    
56          Move        21          27          0                       00                    
57          MakeRecord  22          6           28                      00                    
58          IdxInsert   6           28          0                       00                    
59          IfZero      3           62          0                       00                    
60          AddImm      3           -1          0                       00                    
61          Goto        0           64          0                       00                    
62          Last        6           0           0                       00                    
63          Delete      6           0           0                       00                    
64          Next        8           24          0                       00                    
65          Close       7           0           0                       00                    
66          Close       0           0           0                       00                    
67          Close       8           0           0                       00                    
68          Close       2           0           0                       00                    
69          Close       3           0           0                       00                    
70          Close       4           0           0                       00                    
71          Close       5           0           0                       00                    
72          OpenPseudo  9           21          9                       00                    
73          Sort        6           89          0                       00                    
74          AddImm      2           -1          0                       00                    
75          IfNeg       2           77          0                       00                    
76          Goto        0           88          0                       00                    
77          Column      6           5           21                      00                    
78          Column      9           0           12                      20                    
79          Column      9           1           13                      00                    
80          Column      9           2           14                      00                    
81          Column      9           3           15                      00                    
82          Column      9           4           16                      00                    
83          Column      9           5           17                      00                    
84          Column      9           6           18                      00                    
85          Column      9           7           19                      00                    
86          Column      9           8           20                      00                    
87          ResultRow   12          9           0                       00                    
88          Next        6           74          0                       00                    
89          Close       9           0           0                       00                    
90          Halt        0           0           0                       00                    
91          Transactio  0           0           10          0           01                    
92          TableLock   0           11          0           street_nam  00                    
93          TableLock   0           13          0           street_add  00                    
94          TableLock   0           9           0           postal_cod  00                    
95          TableLock   0           7           0           cities      00                    
96          TableLock   0           4           0           municipali  00                    
97          TableLock   0           2           0           counties    00                    
98          Goto        0           1           0                       00

EXPLAIN for query ... sn.name LIKE ... :

sqlite> EXPLAIN SELECT sn.name, sa.house_number, sa.entrance, pc.postal_code, ci.name, mu.name, co.name, sa.latitude, sa.longitude FROM street_addresses AS sa INNER JOIN street_names   AS sn ON sa.street_name  = sn.id INNER JOIN postal_codes   AS pc ON sa.postal_code  = pc.id INNER JOIN cities         AS ci ON sa.city         = ci.id INNER JOIN municipalities AS mu ON sa.municipality = mu.id INNER JOIN counties       AS co ON mu.county       = co.id WHERE sn.name LIKE "FORNEBUVEIEN" AND sa.house_number = 13 ORDER BY ci.name ASC, sn.name ASC, sa.house_number ASC, sa.entrance ASC LIMIT 0, 100; 
addr        opcode      p1          p2          p3          p4          p5          comment   
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
0           Init        0           88          0                       00                    
1           OpenEpheme  6           6           0           k(4,B,B,B,  00                    
2           Integer     100         1           0                       00                    
3           Integer     0           2           0                       00                    
4           MustBeInt   2           0           0                       00                    
5           IfPos       2           7           0                       00                    
6           Integer     0           2           0                       00                    
7           Add         1           2           3                       00                    
8           IfPos       1           10          0                       00                    
9           Integer     -1          3           0                       00                    
10          OpenRead    0           13          0           9           00                    
11          OpenRead    1           11          0           2           00                    
12          OpenRead    4           4           0           4           00                    
13          OpenRead    3           7           0           2           00                    
14          OpenRead    5           2           0           2           00                    
15          OpenRead    2           9           0           2           00                    
16          Rewind      0           63          0                       00                    
17          Column      0           1           4                       00                    
18          Ne          5           62          4           (BINARY)    6c                    
19          Column      0           5           6                       00                    
20          MustBeInt   6           62          0                       00                    
21          NotExists   1           62          6                       00                    
22          Column      1           1           9                       00                    
23          Function    1           8           7           like(2)     02                    
24          IfNot       7           62          1                       00                    
25          Column      0           8           10                      00                    
26          MustBeInt   10          62          0                       00                    
27          NotExists   4           62          10                      00                    
28          Column      0           7           11                      00                    
29          MustBeInt   11          62          0                       00                    
30          NotExists   3           62          11                      00                    
31          Column      4           3           12                      00                    
32          MustBeInt   12          62          0                       00                    
33          NotExists   5           62          12                      00                    
34          Column      0           6           13                      00                    
35          MustBeInt   13          62          0                       00                    
36          NotExists   2           62          13                      00                    
37          Column      1           1           14                      00                    
38          Copy        4           15          0                       00                    
39          Column      0           2           16                      00                    
40          Column      2           1           17                      00                    
41          Column      3           1           18                      00                    
42          Column      4           1           19                      00                    
43          Column      5           1           20                      00                    
44          Column      0           3           21                      00                    
45          RealAffini  21          0           0                       00                    
46          Column      0           4           22                      00                    
47          RealAffini  22          0           0                       00                    
48          MakeRecord  14          9           7                       00                    
49          Column      3           1           23                      00                    
50          Column      1           1           24                      00                    
51          Column      0           1           25                      00                    
52          Column      0           2           26                      00                    
53          Sequence    6           27          0                       00                    
54          Move        7           28          0                       00                    
55          MakeRecord  23          6           29                      00                    
56          IdxInsert   6           29          0                       00                    
57          IfZero      3           60          0                       00                    
58          AddImm      3           -1          0                       00                    
59          Goto        0           62          0                       00                    
60          Last        6           0           0                       00                    
61          Delete      6           0           0                       00                    
62          Next        0           17          0                       01                    
63          Close       0           0           0                       00                    
64          Close       1           0           0                       00                    
65          Close       4           0           0                       00                    
66          Close       3           0           0                       00                    
67          Close       5           0           0                       00                    
68          Close       2           0           0                       00                    
69          OpenPseudo  7           7           9                       00                    
70          Sort        6           86          0                       00                    
71          AddImm      2           -1          0                       00                    
72          IfNeg       2           74          0                       00                    
73          Goto        0           85          0                       00                    
74          Column      6           5           7                       00                    
75          Column      7           0           14                      20                    
76          Column      7           1           15                      00                    
77          Column      7           2           16                      00                    
78          Column      7           3           17                      00                    
79          Column      7           4           18                      00                    
80          Column      7           5           19                      00                    
81          Column      7           6           20                      00                    
82          Column      7           7           21                      00                    
83          Column      7           8           22                      00                    
84          ResultRow   14          9           0                       00                    
85          Next        6           71          0                       00                    
86          Close       7           0           0                       00                    
87          Halt        0           0           0                       00                    
88          Transactio  0           0           10          0           01                    
89          TableLock   0           13          0           street_add  00                    
90          TableLock   0           11          0           street_nam  00                    
91          TableLock   0           4           0           municipali  00                    
92          TableLock   0           7           0           cities      00                    
93          TableLock   0           2           0           counties    00                    
94          TableLock   0           9           0           postal_cod  00                    
95          Integer     13          5           0                       00                    
96          String8     0           8           0           FORNEBUVEI  00                    
97          Goto        0           1           0                       00
Hubro
  • 56,214
  • 69
  • 228
  • 381
  • This question could help you: http://stackoverflow.com/questions/8584499/sqlite-should-like-searchstr-use-an-index – Peter Lang Apr 29 '14 at 06:40
  • @PeterLang: I tried using the proposed solution: `PRAGMA case_sensitive_like = OFF` but it made no difference in the speed. – Hubro Apr 29 '14 at 06:52
  • Is there a reason for using `LIKE` without wildcards? – Turophile Apr 29 '14 at 06:57
  • @Turophile: This is a query for a search function that should support wildcards, there just weren't any in this example. – Hubro Apr 29 '14 at 06:57
  • Please paste your queries and explains directly into the question, rather than posting to an outside service like pastebin. The external code can go away at any time, and there is no version control over it like with SO questions. Therefore, future readers maynot be able to see the external code and thus this question will be useless or not make much sense. They certainly will not be able to find this question via keyword searching. **StackOverflow is as much about future reference for others as it is about you getting your question answered today.** – Andy Lester Apr 29 '14 at 13:47
  • @AndyLester: I'm not sure this case applies to that rule. There's quite a lot of information that is just *possibly* helpful to solving the question. Never the less I did as you asked – Hubro Apr 29 '14 at 13:57
  • Thank you. Now readers of your question and the answers a year from now will have all the information that we have today. – Andy Lester Apr 29 '14 at 14:00

2 Answers2

3

The documentation says LIKE requires a case-insensitive index:

CREATE INDEX ci_name ON street_names(name COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT ... sn.name LIKE "FORNEBUVEIEN" ...;
0|0|1|SEARCH TABLE street_names AS sn USING COVERING INDEX ci_name (name>? AND name<?)
...

Alternatively, use GLOB to be able to use the case-sensitive index:

EXPLAIN QUERY PLAN SELECT ... sn.name GLOB "FORNEBUVEIEN" ...;
0|0|1|SEARCH TABLE street_names AS sn USING COVERING INDEX sqlite_autoindex_street_names_1 (name>? AND name<?)
...
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Shouldn't `PRAGMA case_sensitive_like = OFF` have worked then? – Hubro Apr 29 '14 at 08:32
  • Yes, but it affects all LIKEs; to use a case-sensitive search, better use GLOB. – CL. Apr 29 '14 at 08:34
  • Case sensitivity is completely irrelevant in my database. All strings in the database and my queries are upper case. `PRAGMA case_sensitive_like = OFF` didn't fix the speed issue. Is it still possible that adding `COLLATE NOCASE` to my indexes will= – Hubro Apr 29 '14 at 08:36
  • If you do not need case insensitivity, then don't use LIKE (but GLOB, which works with a normal index). – CL. Apr 29 '14 at 09:31
  • I had no idea `GLOB` was a thing. Thanks!!! Will accept as soon as you add it to the answer. – Hubro Apr 29 '14 at 13:26
0

I am not an sqlite expert, but in any SQL dialect, LIKE is never going to be as fast as =. But perhaps you can rearrange the query to optimise it:

SELECT sn.name, sa.house_number, sa.entrance, pc.postal_code, ci.name, mu.name,
       co.name, sa.latitude, sa.longitude
FROM
  street_addresses AS sa
  INNER JOIN street_names   AS sn ON sa.street_name  = sn.id
  AND sn.name LIKE "FORNEBUVEIEN"
  INNER JOIN postal_codes   AS pc ON sa.postal_code  = pc.id
  AND sa.house_number = 13
  INNER JOIN cities         AS ci ON sa.city         = ci.id
  INNER JOIN municipalities AS mu ON sa.municipality = mu.id
  INNER JOIN counties       AS co ON mu.county       = co.id
ORDER BY ci.name ASC, sn.name ASC, sa.house_number ASC, sa.entrance ASC
LIMIT 0, 100;

My thinking is that by forcing the evaluation early, there is less data to parse. Of course, if the optimiser is smart enough, it would already optimise the access path.

Turophile
  • 3,367
  • 1
  • 13
  • 21