MySQL Forums
Forum List  »  Performance

Re: InnoDB CPU Spikes
Posted by: Rick James
Date: April 06, 2009 08:01PM

The number of unique users for a given day is not equal to the sum of the uniques for each of the 24 hours, right? So, yes, it requires a different algorithm. But it can still use a summary table, though perhaps one just for such. How about this:

The table has just two fields:
* hour (timestamp truncated to the hour)
* user
KEY (hour, user) -- note: not PRIMARY OR UNIQUE (see below)

The insertion is
INSERT INTO summary2
SELECT DISTINCT ... as hour, user FROM main_table
WHERE id BETWEEN $leftoff and ...;

The uniques for one hour or day or month:
SELECT count(DISTINCT user) FROM summary2
WHERE hour >= ? AND hour < DATE_ADD(?, INTERVAL 1 xxx);
with xxx = HOUR or DAY or MONTH

The devil's in the details. Since you can't trust to do the summarization exactly on the hour boundary, bit the bullet and allow some dups in the table. (Well, I guess INSERT IGNORE would work, too.)

Options: ReplyQuote


Subject
Views
Written By
Posted
7416
March 18, 2009 02:43AM
2750
March 18, 2009 03:08AM
2619
March 18, 2009 08:29PM
2574
March 19, 2009 04:48AM
2728
March 19, 2009 10:46AM
2503
March 19, 2009 03:47AM
2653
March 19, 2009 03:55AM
2520
March 19, 2009 04:49AM
2492
March 19, 2009 10:20AM
2195
March 30, 2009 01:25AM
2270
March 30, 2009 07:13PM
2295
March 31, 2009 02:31AM
2520
March 31, 2009 08:12AM
2192
April 01, 2009 03:27AM
2243
April 01, 2009 10:01AM
2351
April 01, 2009 10:22AM
2224
April 01, 2009 03:45AM
2507
April 01, 2009 11:57PM
2402
April 06, 2009 01:23AM
Re: InnoDB CPU Spikes
2363
April 06, 2009 08:01PM
2145
April 07, 2009 12:14AM
2296
April 08, 2009 04:36PM
2315
April 16, 2009 07:02AM


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.