MySQL Forums
Forum List  »  Performance

InnoDB physical order is by primary key
Posted by: James Day
Date: December 16, 2004 01:45AM

InnoDB physical order is by primary key, so you're going to end up doing a lot of disk seeks for anything not in cache. Try changing the primary key to user_id, hit_id if it's your most time-sensitive operation. That'll get you many results for the user in a single page read. The negative side of this is that it'll make the other keys larger because the primary key is an invisble field in all keys.

It won't be good enough for your production use (because the effect is temporary) but you can alter table order by user_id to get an idea of the possible benefit.

If you don't need the referrer much of the time you might see some benefit from putting that in a different table and doing a join when you need it. Smaller data so more records per database page and greater possible cache efficiency. Might get you twice as many records per page. Referrers tend to be a limited subset of possible string values, so you might also wonder about using some form of referrer ID instead of the actual referrer, at least for selected common referrers.

Options: ReplyQuote

Written By
December 13, 2004 04:18PM
December 14, 2004 11:45AM
December 14, 2004 08:06PM
December 14, 2004 12:55PM
December 14, 2004 11:35PM
InnoDB physical order is by primary key
December 16, 2004 01:45AM
December 15, 2004 01:19PM
December 15, 2004 01:39PM
December 15, 2004 01:53PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.