Indexes with sorting 1

[nego78]

Hi

i've got a table
Id,Name,Status,DateAdded,Rate

So i'd like to select 5 newest records with status = 1

SELECT * FROM Table WHERE Status = 1 ORDER BY DateAdded DESC LIMIT 5

so i need here index on (Status,DateAdded)

later i query for 100 top rated

SELECT * FROM Table WHERE Status = 1 ORDER BY Rate DESC LIMIT 100

so i need here index on (Status,Rate)


And now technical questions
Which is better:

CREATE INDEX IX_A ON Table (Status,DateAdded);
CREATE INDEX IX_B ON Table (Status,Rate);

or

CREATE INDEX IX_A ON Table (Status);
CREATE INDEX IX_B ON Table (Rate);
CREATE INDEX IX_C ON Table (DateAdded);

gry online

 

[TonyGroves]

With your first solution, the required records would be all together in the index, so the query would be very fast.

With the second solution, only the Status index would be used, and the qualifying records would then be searched to pick out the top ones. This would be much slower.

 

[nego78]

But i'm worried about one thing

Do i need index on Status ?
I think that will be about 190000 records with Status = 1 and only 500-1000 records with Status = 0

So maybe i'll delete index from status and query like that

index:
CREATE INDEX IX_C ON Table (DateAdded);

SELECT * FROM Table USE (IX_C) WHERE Status = 1 ORDER BY DateAdded DESC LIMIT 5

is this a good option ?

gry online

 

[TonyGroves]

I have a table similar to yours (of sales transactions) which has two fields `cancelled` and `trandate`. Only about 1000 of the 85000 records are cancelled. There is an index on `trandate`.

If I do a similar query to yours, the `trandate` index is used, and the query is fast.

However, if I also create an index on `cancelled`, only the `cancelled` index is used, and the query is much slower.

And, if I create a combined index on `cancelled` and `trandate`, that index is used, and the speed is about as fast as the first test.

So, you're right, it looks like dropping the Status index could be a good idea.