1
SELECT UNIX_TIMESTAMP(a.timestamp) AS timestamp, a.adid, a.adtitle, a.cityid, a.price, 
a.area, a.addesc, a.subcatid, ct.cityname, p.picfile, scat.subcatname, cat.catid, cat.catname 
FROM xzclf_ads a USE INDEX (id_index) 
INNER JOIN xzclf_cities ct ON a.cityid = ct.cityid 
INNER JOIN xzclf_subcats scat ON a.subcatid = scat.subcatid 
INNER JOIN xzclf_cats cat ON scat.catid = cat.catid 
LEFT OUTER JOIN xzclf_adxfields axf ON a.adid = axf.adid 
LEFT OUTER JOIN xzclf_adpics p ON a.adid = p.adid AND p.isevent = '0' 
LEFT OUTER JOIN xzclf_featured feat ON a.adid = feat.adid AND feat.adtype = 'A' 
WHERE scat.catid = 10 
AND a.enabled = '1' 
AND a.verified = '1' 
AND a.expireson >= NOW() 
AND a.paid <> '0' 
AND (feat.adid IS NULL OR feat.featuredtill < NOW()) 
GROUP BY a.adid 
ORDER BY a.timestamp DESC 
LIMIT 0, 20

It gives an error: Lost connection to MySQL server during query Is anything wrong with this query?

2 Answers 2

3

There's a good chance the mysql crashed while trying to execute your query. First check the error log to see if it did. Common reason for mysql crash is memory exhaustion and tmp dir space depletion.

2
  • Thanks, but this is related to hosting service, right?
    – George
    Oct 17, 2014 at 9:23
  • Possibly yes. If it's memory issue, check how much memory you are allowed, and try dialing back some important parameters such as innodb_buffer_pool_size, key_cache_size, query_cache_size, etc. You may not even need to use some of values mentioned. Oct 17, 2014 at 18:40
0

It looks like mysql is crashing while running the query. If there is not enough memory for mysql to run it will crash. Check your buffer sizes , whether it is set too high. If so, decrease the buffer sizes.

If you have repeated failures, run mysql check on the tables that you are using in the query to find if any of the table(s) is corrupted.

to run mysqlcheck

mysqlcheck -u<username> -p<password> database_name
1
  • Thanks, I will try also to create some indexes on the columns used in query to optimize the joins..the initial query takes more than 20 seconds to complete.. Do I have access from PhpMyAdmin to MySql logs?
    – George
    Oct 17, 2014 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.