How To Create a SQL Index to Import ORDER BY performance

Question

Hi,

I have some SQL similar to the following, which joins four tables and then orders the results by the "status" column of the first:

SELECT * 
 FROM a, b, c, d 
 WHERE b.aid=a.id AND c.id=a.cid AND a.did=d.id AND a.did='XXX'
 ORDER BY a.status

It works. However, it's slow. I've worked out this is because of the ORDER BY clause and the lack of any index on table "a".

All four tables have the PRIMARY KEYs set on the "id" column.

So, I know I need to add an index to table a which includes the "status" column but what else does it need to include? Should "bid", "cid" and "did" be in there too?

I've tried to ask this in a general SQL sense but, if it's important, the target is SQLite for use with Gears.

Thanks in advance,

Jake (noob)

Answer 1

I'd be curious as to how you worked out that the problem is 'the ORDER BY clause and the lack of any index on table "a".' I find this a little suspicious because there is an index on table a, on the primary key, you later say.

Looking at the nature of the query and what I can guess about the nature of the data, I would think that this query would generally produce relatively few results compared to the size of the tables it's using, and that thus the ORDER BY would be extremely cheap. Of course, this is just a guess.

Whether an index will even help at all is dependent on the data in the table. What indices your query optimizer will use when doing a query is dependent on a lot of different factors, one of the big ones being the expected number of results produced from a lookup.

One thing that would help a lot is if you would post the output of EXPLAINing your query.

Answer 2

I would say it's slow because the engine is doing scans all over the place instead of seeks. Did you mean to do SELECT a.* instead? That would be faster as well, SELECT * here is equivalent to a.*, b.*, c.*, d.*.

You will probably get better results if you put a separate index on each of these columns:

• a.did (so that a.did = 'XXX' is a seek instead of a scan, also helps a.did = d.id)
• a.cid (for a.cid = c.id)
• b.aid (for a.id = b.aid)

You could try adding Status to the first and second indexes with ASCENDING order, for additional performance - it doesn't hurt.

Answer 3

have you tried joins?

select * from a inner join b on a.id = b.aid inner join c on a.cid = c.id inner join d on a.did=d.id where a.did='XXX' ORDER BY a.status

the correct use of joins (left, richt, inner, outer) depends on structure of tables

hope this helps