MySQL query runs very slow on large table

Question

I am trying to run the following query on a very large table with over 90 million of rows increasing

SELECT COUNT(DISTINCT device_uid) AS cnt,  DATE_FORMAT(time_start, '%Y-%m-%d') AS period 
FROM game_session 
WHERE account_id = -2 AND DATE_FORMAT(time_start '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()
GROUP BY period 
ORDER BY period DESC

I have the following table structure:

CREATE TABLE `game_session` (
  `session_id` bigint(20) NOT NULL,
  `account_id` bigint(20) NOT NULL,
  `authentification_type` char(2) NOT NULL,
  `source_ip` char(40) NOT NULL,
  `device` char(50) DEFAULT NULL COMMENT 'Added 0.9',
  `device_uid` char(50) NOT NULL,
  `os` char(50) DEFAULT NULL COMMENT 'Added 0.9',
  `carrier` char(50) DEFAULT NULL COMMENT 'Added 0.9',
  `protocol_version` char(20) DEFAULT NULL COMMENT 'Added 0.9',
  `lang_key` char(2) NOT NULL DEFAULT 'en',
  `instance_id` char(100) NOT NULL,
  `time_start` datetime NOT NULL,
  `time_end` datetime DEFAULT NULL,
  PRIMARY KEY (`session_id`),
  KEY `game_account_session_fk` (`account_id`),
  KEY `lang_key_fk` (`lang_key`),
  KEY `lookup_active_session_idx` (`account_id`,`time_start`),
  KEY `lookup_finished_session_idx` (`account_id`,`time_end`),
  KEY `start_time_idx` (`time_start`),
  KEY `lookup_guest_session_idx` (`device_uid`,`time_start`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

How can I optimize this?

Thank for your answer

There is lot of guessing going on here. You might get some more accurate answers if you can post some EXPLAIN results. — Albin Sunnanbo
– Albin Sunnanbo, Commented Dec 2, 2011 at 21:05
@Albin: There is not much to guess. WHERE DATE_FORMAT(time_start, '%Y-%m-%d') BETWEEN x AND y and GROUP BY DATE_FORMAT(time_start, '%Y-%m-%d') will kill a 90M rows query, no matter what indexes you have. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Dec 2, 2011 at 21:23

Albin Sunnanbo · Accepted Answer · 2011-12-02 21:15:05Z

3

DATE_FORMAT(time_start '%Y-%m-%d') sounds expensive.
Every calculation on a column reduces the use of indexes. You probably run in to a full index scan + calculation of DATE_FORMAT for each value instead of a index lookup / range scan.

Try to store the computed value in the column (or create a computed index if mysql supports it). Or even better rewrite your conditions to compare directly to the value stored in the column.

edited Dec 2, 2011 at 21:15

answered Dec 2, 2011 at 20:55

Albin Sunnanbo

47.2k8 gold badges72 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michael Krelin - hacker Over a year ago

There's no need to store computed value in the column, it's easier to manipulate the values it is compared to.

Albin Sunnanbo Over a year ago

@MichaelKrelin-hacker That is of course the best if possible, but I assume the DATE_FORMAT is used to truncate time-part from the column. How would you do that by manipulation of the values it is compared to?

Michael Krelin - hacker Over a year ago

Truncate the lower bound and bump the higher to the next day?

Albin Sunnanbo Over a year ago

@MichaelKrelin-hacker, Ah, you´re right. I'm used to SQL Server where GETDATE() returns date including time of day, but now I see that CURDATE() only returns the datepart. Then I agree with you: away with the DATE_FORMAT.

Michael Krelin - hacker Over a year ago

Oh, then it's even easier, then you don't have to truncate the lower bound :) What I've proposed would actually work for full timestamp — I'm not a frequent mysql user either (though not sql server user) so I considered all possibilities :)

Michael Krelin - hacker · Accepted Answer · 2011-12-02 20:52:34Z

2

Well, 90mlns is a lot, but I suspect it doesn't use the start_time_idx because of the manipulations, which you can avoid (you can manipulate the values you compare it with with, it also must be done only once per query if mysql is smart enough), have you checked EXPLAIN?

answered Dec 2, 2011 at 20:52

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

2 Comments

user1078191 Over a year ago

You're wright, it doesn't use the start_time_idx, it uses lookup_finished_session_idx and I can' t figure out why

Michael Krelin - hacker Over a year ago

I've just told you why :) Try avoiding formatting, see my comment to Albin's answer on how.

user187228 · Accepted Answer · 2011-12-02 20:54:13Z

1

You may want to group and sort by time_start instead of the period value you create when the query is run. Sorting by period requires all of those values to be generated before any sorting can be done.

answered Dec 2, 2011 at 20:54

user187228

1 Comment

Michael Krelin - hacker Over a year ago

By this time, I think there are less records left after filtering by where condition. But of course having some more input on the data and outcome (as well as intermediate count) would be helpful.

Sergii Kudriavtsev · Accepted Answer · 2011-12-02 20:54:35Z

1

I'd change

BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()

to

> (CURDATE() - INTERVAL 90 DAY)

You don't have records from future, do you?

answered Dec 2, 2011 at 20:54

Sergii Kudriavtsev

10.5k5 gold badges48 silver badges71 bronze badges

Comments

Benjam · Accepted Answer · 2011-12-02 20:58:33Z

1

Try swapping out your WHERE clause with the following: WHERE account_id = -2 AND time_start BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()

MySQL will still catch the dates between, the only ones you'll need to worry about are the ones from today, which might get truncated due to technically being greater than midnight.

You can fix that by incrementing the second CURDATE( ) with CURDATE( ) + INTERVAL 1 DAY

answered Dec 2, 2011 at 20:58

Benjam

5,3663 gold badges31 silver badges37 bronze badges

1 Comment

ypercubeᵀᴹ Over a year ago

Correct but don't use BETWEEN. Use >= and < CURDATE( ) + INTERVAL 1 DAY

ypercubeᵀᴹ · Accepted Answer · 2011-12-02 21:16:15Z

1

Change the query to:

SELECT COUNT(DISTINCT device_uid) AS cnt
     , DATE_FORMAT(time_start, '%Y-%m-%d') AS period 
FROM game_session 
WHERE account_id = -2 
  AND time_start >= CURDATE() - INTERVAL 90 DAY 
  AND time_start <  CURDATE() + INTERVAL 1 DAY
GROUP BY DATE(time_start) DESC

so the index of (account_id, time_start) can be used for the WHERE part of the query.

If it's still slow - the DATE(time_start) does not look very good for performance - add a date_start column and store the date part of time_start.

Then add an index on (account_id, date_start, device_uid) which will further improve performance as all necessary info - for the GROUP BY date_start and the COUNT(DISTINCT device_uid) parts - will be on the index:

SELECT COUNT(DISTINCT device_uid) AS cnt
     , date_start                 AS period 
FROM game_session 
WHERE account_id = -2 
  AND date_start BETWEEN CURDATE() - INTERVAL 90 DAY 
                     AND CURDATE()
GROUP BY date_start DESC

edited Dec 2, 2011 at 21:16

answered Dec 2, 2011 at 21:03

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

1 Comment

user1078191 Over a year ago

Thank you I' m going to try that

Collectives™ on Stack Overflow

MySQL query runs very slow on large table

6 Answers 6

5 Comments

2 Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

2 Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related