InnoDB physical order is by primary key

[James Day]

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.