Mysql – Super simple thesql query taking too long

MySQLperformancequery-performance

I have the following:

SELECT * FROM autorepair
WHERE zip_code IN (91316,91356,91416,91426,91436)
AND specialty = 'transmission'
ORDER BY ABS(91316 - `zip_code`)
LIMIT 0,10

Indexes:

-zip_code
-specialty

How can I improve the performance on queries like these?

BTW, I'm running this on XAMPP.

Best Answer

Create a compound index as follows:

ALTER TABLE autorepair ADD INDEX specialty_zipcode_ndx (specialty,zipcode);

and drop the single column specialty index

ALTER TABLE autorepair DROP INDEX specialty;

Related Solutions

Thesql taking too long to send data

For anyone who stumble upon this question and wondering, even without upgrade of RAM, why sending data was taking so much longer. It is because sending data actually include time of searching the data that is to be sent.

https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Mysql – Why is this simple join taking so long

Look at the original query

select count(page_views.id) as views
from playpack_media
join page_views on page_views.itemId=playpack_media.media_Id
where playpack_media.playpack_id = 1
    and page_views.started_at BETWEEN '2014-06-23' and '2014-07-07'

You should do a couple of things.

First, create to compound indexes

ALTER TABLE page_views     ADD INDEX started_at_itemid_ndx    (started_at,itemid);
ALTER TABLE playpack_media ADD INDEX playpack_id_media_Id_ndx (playpack_id,media_Id)

Try to refactor the query

SELECT COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId;

Perhaps breaking down the views by ItemID

SELECT IFNULL(A.media_id,'Total') Media,COUNT(1) as views FROM
(
    SELECT media_Id FROM playpack_media
    WHERE playpack_id = 1
) A INNER JOIN
(
    SELECT itemId FROM page_views WHERE
    started_at BETWEEN '2014-06-23' and '2014-07-07'
) B ON A.media_Id = B.itemId
GROUP BY A.media_id WITH ROLLUP;

Best Answer

Related Solutions

Thesql taking too long to send data

Mysql – Why is this simple join taking so long

Related Question