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 ?