Mysql optimization

Question

I'm currently trying to optimize a MYSQL statement that is taking quite some time. The table this is running on is 600k+ and the query is taking over 10 seconds.

SELECT DATE_FORMAT( timestamp, '%Y-%m-%d' ) AS date, COUNT( DISTINCT (
email
) ) AS count
FROM log
WHERE timestamp > '2009-02-23'
AND timestamp < '2020-01-01'
AND TYPE = 'play'
GROUP BY date
ORDER BY date DESC

I've just indexes on timestamp and type and also one on timestamp_type (type_2).

Here is the explain results, the problem seems to be a file sort but I don't know how to get around this...

id: 1
select_type: SIMPLE
table: log
type: ref
possible_keys: type,timestamp,type_2
key: type_2
key_len: 1
ref: const
rows: 226403
Extra: Using where; Using filesort

Thanks

Could you show your current indexe definitions with SQL code, so there is no doubt about how they are set up. Also, what data quantities are we talking about? (How many rows, how many "TYPE"s, how many rows per TYPE and timestamp?) — Brimstedt
– Brimstedt, Commented Jul 8, 2009 at 11:08

Greg · Accepted Answer · 2009-07-08 10:33:41Z

4

Things to try:

Have a separate date column (indexed) and use that instead of your timestamp column
Add an index across type and date
Use BETWEEN (don't think it will affect the speed but it's easier to read)

So ideally you would

Create a date column and fill it using UPDATE table SET date = DATE(timestamp)
Index across type and date
Change your select to ... type = ? AND date BETWEEN ? AND ?

answered Jul 8, 2009 at 10:33

Greg

323k55 gold badges378 silver badges338 bronze badges

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

4 Comments

Maciej Łebkowski Over a year ago

converting the type column from varchar to int would help a little I guess.

Andomar Over a year ago

+1 Exactly, MySQL can't use the index if you're grouping on a computed column

Brian Over a year ago

However, depending on how much of the table this is, it may still be more efficient for it to use a full table scan. I know the percentage of the table utilized to merit a full scan is always suprisingly less than I would thing.

Brian Over a year ago

Between is end point inclusive, be wary of this if rewriting.

Brian · Accepted Answer · 2009-07-08 11:29:35Z

0

Try rewriting to filter on TYPE alone first. Then apply your date range and aggregates. Basically create an inline view that filters type down. I know it's likely that the optimizer is doing this already, but when trying to improve performance I find it's helpful to be very certain of what things are happening first.

answered Jul 8, 2009 at 11:29

Brian

2,2552 gold badges24 silver badges39 bronze badges

Comments

Hitanshu Malhotra · Accepted Answer · 2017-05-28 07:02:41Z

0

DATE_FORMAT will not utilizing the indexes.
1. You can still use the below query to utilize the index on timestamp column
  
  SELECT timestamp AS date, COUNT( DISTINCT ( email ) ) AS count FROM log WHERE timestamp > '2009-02-23 00:00:00' AND timestamp < '2020-01-01 23:59:59' AND TYPE = 'play' GROUP BY date ORDER BY date DESC
2. Format the datetime value to date while printing/using

answered May 28, 2017 at 7:02

Hitanshu Malhotra

772 bronze badges

Collectives™ on Stack Overflow

Mysql optimization

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related