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

[Aftab Khan]

>select adId, count(userUId) from UserVisits where adId < 25000 group by adId;

Please provide output of:

EXPLAIN select adId, count(userUId) from UserVisits where adId < 25000 group by adId\G

>Usually, the query runs for ever until I break it and do something like:
select count(*) from UserVisits
>which takes about 15 minutes after which the original query just take 2 minutes

This is expected because data pages get cached in either O/S cache or in database buffers (e.g. innodb buffer pool), so the subsequent select queries are fast


Can we see output of following:

SHOW GLOBAL STATUS LIKE 'innodb_%';



>PRIMARY KEY (`userUId`,`adId`)

What other queries (SELECTs) do you run against this table?


>`validImps` int(4) unsigned NOT NULL,
>`validClicks` int(2) unsigned NOT NULL,
>`validImpsToday` int(4) unsigned NOT NULL,
>`validClicksToday` int(2) unsigned NOT NUL

> int(4) vs int(2), each require 4 bytes storage

You may use MEDIUMINT UNSIGNED (storage: 3 bytes, max value 16 million) for above columns

>`adId` int(10) unsigned NOT NULL
You should consider SMALLINT UNSIGNED (storage: 2 bytes, max value 64k )

Note: Design your tables to minimize their space on the disk. This can result in huge improvements by reducing the amount of data written to and read from disk. Smaller tables normally require less main memory while their contents are being actively processed during query execution. Any space reduction for table data also results in smaller indexes that can be processed faster



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