Very slow request with small difference 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 ?
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.)