7

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

 3 years ago
source link: https://www.codesd.com/item/mysql-query-very-slow-any-recommendation-on-how-to-optimize.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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

advertisements

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:

BvAMp.png
gVNQ9.png

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:

hHYul.png

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?

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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK