CODEWITHSUNDEEP
sqlsqlite
kai
kai

Reputation: 1201

SQL query - need improvement

I have a SQL statement like following, I only have around 20000 records in tableA and 1000 in tableB, but the query takes around 5 seconds, is there any simple way to improve the query speed, thanks!! (please note that there could be more than two tables to UNION)

I think the performance bottleneck doesn't result from 'UNION', if I just query from tableA, the time is almost the same, any ideas??

SELECT * 
FROM (SELECT * FROM tableA 
      UNION ALL 
      SELECT * FROM tableB) AS table 
 WHERE id >= 1
 AND deleted = '0' 
 ORDER BY time DESC, id DESC 
 LIMIT 100;

Upvotes: 0

Views: 64

Answers (2)

McAden
McAden

Reputation: 13970

View the execution plan and see what's happening. If you look at this fiddle you'll see notable differences in how it evaluates.

Before indexes:

SEARCH TABLE tableA USING INTEGER PRIMARY KEY (rowid>?) (~25000 rows)
USE TEMP B-TREE FOR ORDER BY
SEARCH TABLE tableB USING INTEGER PRIMARY KEY (rowid>?) (~25000 rows)
USE TEMP B-TREE FOR ORDER BY
COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

After indexes:

SEARCH TABLE tableA USING INDEX IX_tableA (deleted=?) (~3 rows)
USE TEMP B-TREE FOR ORDER BY
SEARCH TABLE tableB USING INDEX IX_tableB (deleted=?) (~3 rows)
USE TEMP B-TREE FOR ORDER BY
COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

One way is to remove the subquery and move the conditions closer to the tables:

(SELECT *
 FROM tableA
 WHERE id >= 1 AND deleted = '0'
) UNION ALL
(SELECT *
 FROM tableB
 WHERE id >= 1 AND deleted = '0'
) 
ORDER BY time DESC, id DESC
LIMIT 100;

Then create two indexes:

create index tableA_deleted_id on tableA(deleted, id);
create index tableB_deleted_id on tableB(deleted, id);

Upvotes: 3

Related Questions