当前位置: 动力学知识库 > 问答 > 编程问答 >

What causes sqlite join contraints with OR clauses to be significantly slower?

问题描述:

Here are two sqlite queries:

SELECT * FROM items JOIN licenses ON items.id=licenses.id OR items.type=licenses.type;

This query doesn't use OR, it uses UNION

SELECT * FROM items JOIN licenses ON items.id=licenses.id UNION SELECT * FROM items JOIN licenses ON items.type=licenses.type;

Assuming I have an index in the licenses table on id and an index in the licenses table on type shouldn't the first query that uses an OR be only a tiny bit slower?

I am seeing that the first query is approximately 20 times slower than the second query in Sqlite, what is the cause for that?

I would expect the internal plan to look something like this for the first query:

  • For each row in the items table:
  • Take the value from the id column of the items table and use it to lookup all rows in the licenses table with that id, call that set of matching rows A.
  • Take the value from the type column of the items table and use it to lookup all rows in the licenses table with that type, call that set of matching rows A'.

    Combine A and A' and eliminate any duplicate rows. Add the result in the list of result rows

网友答案:

For doing joins, SQLite supports only nested loop joins on two tables (which can be optimized with indexes). As explained in The SQLite Query Planner and Query Planning, doing joins with two tables at once is not one of the supported optimizations.

分享给朋友:
您可能感兴趣的文章:
随机阅读: