Improving efficiency of SQL selects and joins in sqlite

Question

I'm working on a personal project focusing on analysis of text in a database. My intent is to do something interesting and learn about SQL and sqlite. So with my novice abilities in mind, I'd like to get advice on doing this more efficiently.

Say, for example, I want to pick out the types of food in an article A. I parse my article, and if I find a food F, then I add F to table items. Then I add A.id and F.id to results. When I parse my article and find a food G that already exists in items, all I do is add A.id and G.id to results.

So my schemas look something like the following:

• articles: id, article
• results: id, item_id, article_id
• items: id, foodtype, food

If I want to find all the articles that talk about oranges and grapes and any vegetable, then I'd start with something like this:

SELECT * 
  FROM articles 
INNER JOIN results ON articles.id = results.article_id  
INNER JOIN items ON results.item_id = items.id

and add:

WHERE foodtype='vegetable' OR food='orange' OR food='grape'

In reality, my database is much bigger. There are thousands of articles and over a hundred thousand extracted "foods." Most of these queries in which I join 3 tables don't return, even if I limit things to 100 results. I've tried creating an index on fields that are commonly in my WHERE clauses, like food and foodtype, but have seen no improvement.

Are there improvements that I can make to my database or query?

Answer 1

FIrst of all SELECT * is evil . No matter how many indexes you build you query will not be covered (unless you index the whole table which then makes Index scan and table scan same cost). 1. So select on the columns that you want to display. 2. Add custered index on the id columns 3. add non clustered on the column in WHERE clause 4. Put a covering index on the columns in your select query.

The best way to tune a query is to look at the execution plan and look at the bottleneck step but since it is not there in your question this is the best guess I can take

Answer 2

Always inner join the smallest table first. I suspect you will not have as many items as articles (maybe?). So it should be "small inner join bigger inner join biggest".

Answer 3

Retrieve Only The Columns You Need

The first problem with the query is that SELECT * is returning all columns from all tables joined in the query. That means the values in the JOIN criteria, on both sides of the evaluation, are being returned. It's better to write out the actual columns you need, because all three you listed have an id column--which complicates correct value retrieval unless using ordinal position (not a good practice--change position, data retrieval isn't what it should be).

Using table aliases minimizes what you need to use to reference a specific table:

SELECT a.article 
  FROM ARTICLES a
  JOIN RESULTS r ON r.article_id = a.id
  JOIN ITEMS i ON i.id = r.item_id

Indexing

Indexing the foreign keys--what you are using to for JOIN criteria, should be the second thing on the list after the primary key for the table.

Then you have to periodically run the ANALYZE command because the statistics are...

...not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.

These statistics are what the optimizer uses for it's query decision, along with the presence of indexes.

ORs Are Notoriously Bad for Performance

You might try re-writing the query so it doesn't use ORs with a UNION:

SELECT a.article 
  FROM ARTICLES a
  JOIN RESULTS r ON r.article_id = a.id
  JOIN ITEMS i ON i.id = r.item_id
 WHERE i.foodtype = 'vegetable'
UNION 
SELECT a.article 
  FROM ARTICLES a
  JOIN RESULTS r ON r.article_id = a.id
  JOIN ITEMS i ON i.id = r.item_id
 WHERE i.food IN ('orange', 'grape')

Be aware that UNION is slower than UNION ALL, because UNION removes duplicates. UNION ALL is faster because it doesn't remove duplicates.

Answer 4

These queries can be amazingly fast in SQLite. I'm doing something comparable

FOODTYPE
foodtypeid integer primary key
foodtypedesc  text

FOOD
foodid integer primary key
foodtypeid integer (indexed)
fooddesc text (indexed)

ARTICLE
articleid integer primary key 
title


ARTICLEFOOD
id integer primary key autoincrement
articleid integer   (indexed)
foodid integer      (indexed)
foodtypeid integer  (indexed) [EDIT: forgot to add this column yesterday)

NOTE: all primary keys are indexed and columns marked for indexing should be indexed.

 select title, foodesc, foodtypedesc
 from articlefood AF
 join article A on AF.articleid=A.articleid
 join FOOD F on AF.foodid = F.foodid and fooddesc
 join FOODTYPE FT on FT.foodtypeid = F.foodtypeid 
 where .....

or you can use inline views which can also be very fast in SQLite given suitable indexes. The following query would return all articles ids that match specified food(s) and specified foodtype(s). The default behavior of UNION is to eliminate duplicate rows, and since we're asking only for articleids (with the intention of joining something to this list of ids) the query below would result in the set of distinct article ids that meet the criteria:

 select articleid from  ARTICLEFOOD 
 JOIN
 (
    select foodid from FOOD where  .... 
 ) as MyFoods
 ON ARTICLEFOOD.foodid = MyFoods.foodid

 UNION

 select articleid from  ARTICLEFOOD 
 JOIN
 (
    select foodtypeid from FOODTYPE where  .... 
 ) as MyFoodTypes
 ON ARTICLEFOOD.foodtypeid = MyFoodTypes.foodtypeid

Tim