Sql Query Not Working in DB

Question

When i try to run sql query, its not working. i get error like,

My Query is below

SELECT cpev.value as Name,cpe.sku,gp.value category_url, ea.value image_path FROM catalog_product_entity cpe inner join (SELECT entity_id,value FROM catalog_product_entity_varchar WHERE attribute_id="71") cpev on cpev.entity_id = cpe.entity_id inner join (SELECT entity_id,value FROM catalog_product_entity_media_gallery) ea on ea.entity_id=cpe.entity_id inner join( SELECT category_id, product_id FROM catalog_category_product) eavv on cpe.entity_id =eavv.product_id inner join( SELECT entity_id,value FROM catalog_category_entity_varchar WHERE attribute_id='57') gp on eavv.category_id=gp.entity_id

Answer 1

Looks like your query is too havy for remote mysql server. Here is optimized query.

SELECT cpev.value as Name,cpe.sku,gp.value category_url, ea.value image_path 
FROM catalog_product_entity cpe 
inner join catalog_product_entity_varchar cpev on cpev.entity_id = cpe.entity_id and  cpev.attribute_id = (SELECT attribute_id FROM eav_attribute where attribute_code = 'name' and entity_type_id = 4)
inner join catalog_product_entity_media_gallery  ea on ea.entity_id=cpe.entity_id 
inner join catalog_category_product eavv on cpe.entity_id =eavv.product_id 
inner join catalog_category_entity_varchar gp on eavv.category_id=gp.entity_id and gp.attribute_id = (SELECT attribute_id FROM eav_attribute where attribute_code = 'url_key' and entity_type_id = 3)

if it will not work too, try to add "LIMIT 10" at the end

Answer 2

Firstly enable mysql's error log so you can find exact error

1.uncheck limit rows in in Edit → Preferences →SQL Queries

or

2.change specific timeouts. IN MY MySQL WorkBench

For me it was under Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 600

Changed the value to 6000.

Also unchecked limit rows as putting a limit in every time I want to search the whole data set gets tiresome.