MySQL: How to index my table to optimize this query?

Question

My table contains history of values relating to objects, it looks like:

create table History (
    object_id  bigint  NOT NULL,
    value      int     NOT NULL,
    date       bigint  NOT NULL
);

How can I index it to optimize following query:

select * from History
    where object_id = ? and date < ?
    order by date desc
    limit ?

zerkms · Accepted Answer · 2012-07-11 23:45:02Z

3

Create composite index object_id + date

CREATE INDEX object_id_date ON History(object_id, `date`);

answered Jul 11, 2012 at 23:45

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

zerkms Over a year ago

@pst: nope, with conditions like key_part1 = const AND key_part2 < const the only order that makes sense is key_part1 + key_part2, as long as range condition prevents of further using of right index part. In this particular case oid + date covers both WHERE condition and ORDER BY (bacause of left-most part is constant)

Ali Shakiba Over a year ago

thanks, does it optimize all 4 parts of my query? is any of them left unoptimized which i should find another way to optimize it?

zerkms Over a year ago

@JohnS: It optimizes WHERE and ORDER BY. Not sure what other 2 parts you're talking about. LIMIT - cannot be optimized, it is just limit. And SELECT * is not optimized - because it is * there

Ali Shakiba Over a year ago

thanks, just another naive question: do you think this is an optimized structure to store and query history of values in a RDB when there are too many objects and history records? any better design pattern you know about?

user166390 Over a year ago

@JohnS Is there a reason to do otherwise [now or in the immediate future]? :)

|

Collectives™ on Stack Overflow

MySQL: How to index my table to optimize this query?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related