Re: How to get acceptable/repeatable performance on huge table

[Aftab Khan]

>Mysql uses only the index anyway, so the other column sizes should be irrelevant(?):

Smaller -> more cacheable -> faster

what's the actual table size?

SHOW TABLE STATUS UserVisits\G

>`lastRequest` datetime NOT NULL
Similarly, consider using timestamp, difference 8bytes vs 4 bytes

>In a report table, the following query often takes 2 minutes (which is fine with us), but sometimes "never" finishes (has been going for a few hours when I cancelled):

When this happens, can you take snapshot of SHOW PROCESSLIST\G and SHOW ENGINE INNODB STATUS\G

Also, please recreate PK, as this would help get rid of TEMPORARY table and filesort used by this sql:

alter table UserVisits drop primary key, add primary key (adId,userUId);



Edited 1 time(s). Last edit at 08/31/2012 05:45AM by Aftab Khan.