CODEWITHSUNDEEP
sqlperformanceoptimizationindexing
Pavle Lekic
Pavle Lekic

Reputation: 1122

indexing the correct columns based on where clauses used

I have a table with messages. So I have columns id, from, to, etc. If I want to show Inbox for a specific user I would write a select statement with

where to = 'username' order by id desc limit 0,20;

This would show first 20 messages for that user. So obviously I should put index on to column, and there is already an index on id column because it is a primary key, but would it be better to set index on (to, id) together?

Upvotes: 3

Views: 108

Answers (1)

MatBailie
MatBailie

Reputation: 86735

Unfortunately the answer is bigger than is appropriate for SO. People have written books on this.

At a simplistic level, having an index on (to, id DESC) will be the most optimal for solving that query. The first field in the index ensures that the data is easy to search for, and that all the records of interest are in one contiguous block. The second field in the index ensures that that contiguous block is pre-sorted, making it trivial to find the first 20 records.

But mainaining that index is also an issue. Such an index may be very prone to fragmentation. Is there capacity to rebuild the index in a nightly maintenance job? And the more indexes you have the larger the disk space overhead. Do you have disk space to have a new index for every query that may need it? And write overheads increase with extra indexes. How often is the table written to and how critical is it to have minimal latency? Adding the fields you will be querying, in addition to the fields you are filtering/searching/joining, also means that you only need to read the index and not also 'join' to the base table. Is that benefit worth the further increases in overheads of having more and broader indexes?

It's a good question to ask, with a very broad answer, of which I've only tickled the surface here.

Upvotes: 3

Related Questions