Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
You don't use Supplier table at all. What happens when you omit that from the FROM clauses? –  dezso Jun 20 '12 at 12:40
1  
I did remove it and it's now very speed ! Thank you very much. I still don't know why it would be that slow only in the third one, and not the second. –  Nicolas Zozol Jun 20 '12 at 12:44
    
Try by also removing the DISTINCT. –  ypercube Jun 20 '12 at 13:00

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer

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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.