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.
Subject
Views
Written By
Posted
3685
December 13, 2004 04:18PM
2446
December 13, 2004 10:42PM
2536
December 14, 2004 11:45AM
2386
December 14, 2004 12:48PM
2345
December 14, 2004 08:06PM
2398
December 14, 2004 12:55PM
2250
December 14, 2004 11:35PM
InnoDB physical order is by primary key
3835
December 16, 2004 01:45AM
2538
December 15, 2004 01:19PM
2450
December 15, 2004 01:39PM
2348
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.