My site is taking 60 secs to load due to Slow Queries

Question

My Sub Domain Magento Site /market is taking a full min to load up.

Upon careful scrutiny of Slow Query Log File provided by Webhost, there's one query that is taking a whopping 50 secs., details below,.

SELECT * FROM `sangyugk_sm_market_quickstart`.`catalog_product_index_price_idx`;
# Time: 160204 1:13:00
# User@Host: sangyugk[sangyugk] @ localhost []
# Thread_id: 592321 Schema: Last_errno: 1160 Killed: 0
# Query_time: 50.489651 Lock_time: 0.000041 Rows_sent: 228858 Rows_examined: 228858 Rows_affected: 0 Rows_read: 228858
# Bytes_sent: 3439001

Any thoughts or comments from more experienced Magento Techies ?

Could this be the query resulting on slow load up ?

There are other numerous Slow Queries that take 5 secs +, but this one caught my attention.

Pls help..

Answer 1

Yes...60 secs is a lot...This is because you are reading 228858 rows from the database. Normally you will set a limit and a limitstart to get only selected records that are required. Not more. but this seems to be a bug.

you need optimize the query...or try indexing the tables to optimize load time.

Answer 2

Yes... you should contact the developer to rewrite the query.

In the query there is select "*". * means querying all the fields.. Avoid * as it is very database intensive process. Instead use only the required field names. Also instead of querying ALL the results, they should query only a limited records and use a loop to do the rest.