mysql Query very slow, any recommendation on how to optimize?

Question

I've recently moved to a dedicated Windows hosting with Plex CPanel through godaddy. I've setup my site and database (MySQL)

I am now recently noticing my database queries are now taking a lot longer than expected.

My tables:

enter image description here

Here is an example:

SELECT a.snapshot_id, a.brand_id, b.brand_id 
FROM snapshots a 
    INNER JOIN brands b 
               ON a.brand_id = b.brand_id 
WHERE DATE(a.date_sent) = '2014/11/10'

using PhpMyAdmin, running this query:

Showing rows 0 - 247 (248 total, Query took 30.1080 seconds.)

that is a long time when this is executed on the site's homepage.

Any suggestions on how I can optimize this? Or is the server really slow?

The snapshots table has over 45K rows.

The EXPLAIN output: enter image description here

Crazy find! When comparing the ROWS and SIZE between shared hosting and dedicated hosting of the same database and table.

Shared: 1,023,459 records @ 1.8GiB Dedicated: 43,916 records @ 4.5GiB

Why is there a discrepancy?

If you want advice optimising a DB query its a good idea to post the schema, and it's a really good idea to post an `EXPLAIN` of your current query. How much data is in that table? — AndySavage, Nov 11 '14 at 20:45

score 1 · Answer 1 · answered Nov 11 '14 at 22:30

1

You are applying the function date() to every row, which is costly.

Instead, make the comparison in a way that doesn't require any conversion function, like this:

WHERE a.date_sent between '2014/11/10 00:00:00' and '2014/11/10 23:59:59'

And make sure you have an index defined on that column, and the foreign key column of the joined table.

answered Nov 11 '14 at 22:30

Bohemian

412,405
93
575
722

I like this approach. I will try it. – Rick Nov 12 '14 at 00:32

score 1 · Accepted Answer · answered Nov 16 '14 at 02:40

1

Thanks All... Seems like the performance issue is not the query... I've optimized the query based on the suggestions above, and have not seen much improvement.

I've since moved to another hosting service and noticed a vast improvement.

answered Nov 16 '14 at 02:40

Rick

555
1
12
30

score 0 · Answer 3 · edited May 23 '17 at 12:05

The problem with your query is that it makes it quite hard for the database optimizer to apply its optimization strategies (e.g indexes) because of the DATE() function being part of the where part of the query. However, a good database engine may be able to provide a speed up if the query is repeated completely unchanged.

Besides the general design recommendation to save date and time separately into different database columns (which probably would be the better way to proceed), there is an idea presented in this answer https://stackoverflow.com/a/95256/3911010 how to avoid the inefficient usage of DATE in the query.

reason for the date function is that the column for date is date + time, i want to return all time of that day. — Rick, Nov 12 '14 at 00:32

score 0 · Answer 4 · answered Nov 11 '14 at 22:32

0

A few things

Make your tables match. One uses a big int. The other an int. Use the same

Second remove the date function and compare to yyyy-mm-dd

Index the snapshots. Brand_I'd column to help with the join

answered Nov 11 '14 at 22:32

exussum

18,275
8
32
65

mysql Query very slow, any recommendation on how to optimize?

4 Answers4