Very slow request with small difference

Question

I use sqlite3 on a Mac Mini. I'm a Java and Web programmer, with some SQL experience, not as much as an expert.

I have three requests. First and second are fast (0.2s), and the third is very slow (7s) when the WHERE clause is the combination of the first ones.

// Fetch all items in a universe (it's a category in marketing terms)

 SELECT item.codeItem, item.designation
 FROM Item item
 WHERE item.universe = 10  
 ORDER BY designation  LIMIT 0,25

// search articles from a supplier (an article may have many prices with different suppliers) //Price table is used as an associative table between Article and Supplier

SELECT DISTINCT item.codeItem, item.designation 
FROM Item item, Price price, Supplier supplier 
WHERE  (price.codeItem = item.codeItem AND price.supplier = 29184)
ORDER BY designation  LIMIT 0,25 ;

// search Article with a supplier in a universe

SELECT DISTINCT item.codeItem, item.designation
FROM Item a, Price t, Supplier supplier  
WHERE item.universe = 10 AND  (price.codeItem = item.codeItem AND price.supplier = 29184)
ORDER BY designation  LIMIT 0,25 ;

First question is : Why is the last one so slow ? I would have thought that adding AND filters could make the request even faster.

Second question is : what would you try first to improve the query. Is a nested SELECT a good solution ?

Answer 1

SHOW CREATE TABLE

EXPLAIN SELECT ...

Possibly desirable:

INDEX(supplier, codeItem)

INDEX(universe, codeItem)

The DISTINCT will force a sort (or other semi-heavy processing)

The ORDER BY will force a sort. And, since it has to happen before the LIMIT, the query will run about the same speed with or without the LIMIT.

Never mind... The real problem is that you are doing a CROSS-JOIN with Supplier! Simply removing the reference to Supplier might suffice.

Suggest you always use JOIN...ON, not "commajoin". That way, you are less likely to fail to connect the tables.

Answer 2

It would be interesting to know if there are indexes on your tables (and if there are, their structure as well). There is the slight possibility that these would help to understand the reason behind the third query being much slower than the second one.

Well, this does not answer this mystery, but apart from the above, you should omit the Supplier table from your queries. Since you don't select any data from it, the only thing it does is multiplying the number of the rows selected (SELECT any_column FROM table1 t1, table2 t2 without further conditions on which t1 row to be joined to which t2 row produces the Cartesian product of the two tables - it can be written as table1 t1 CROSS JOIN table2 t2 in other RDBMSes, however in SQLite CROSS JOIN only fixes the order in which tables are processed). You apparently use SELECT DISTINCT to get rid of the multiplicated results (@ypercube, thanks for the suggestion).

It would be interesting to see whether the speed difference is still there after these modifications. If the answer is 'yes', than again I would try to swap the two parts of the WHERE clause and check the difference again. (Well, SQLite seems to be quite smart avoiding plan gotchas like this, but you never can tell with bees.)