You are here:  » MySql query time out


MySql query time out

Submitted by keshavkshirsagar on Wed, 2015-09-23 06:56 in

Hello Sir

SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice,buy_url,search_name FROM `products` WHERE categoryid IN ('2',..) GROUP BY search_name

This query tacking too much time to get result

My product database site about million records

And It consuming huge resource on server which affect on site speed

Is any alternate way for this SQL Query

Submitted by support on Wed, 2015-09-23 07:52

Hi,

Please see this thread for discussion about MySQL server configuration for large installations - although there is no limit imposed by the script of course ultimately you will reach the limitations of the server capabilities and MySQL in terms of the application level functionality offered (sort, filtering, grouping requirements of the script) etc.

The query is against purely indexed fields and I think with a substantial increase to key_buffer_size and sort_buffer_size (the defaults are very small) you should see a good performance increase - I normally suggest allocating around 1/4 of total server memory equally between them, so for a server instance with say 2GB ram;

key_buffer_size=256M
sort_buffer_size=256M

Ultimately of course, it may be best to consider sub-directory installations (more info and mutli-installation search script)

Cheers,
David.
--
PriceTapestry.com

Submitted by keshavkshirsagar on Wed, 2015-09-23 08:24

Hello David

Should we keep Code and Database different for all sub directory of category ?

Submitted by support on Wed, 2015-09-23 09:25

Hi,

I would suggest the same database ($config_databaseName) but use separate table prefix value ($config_databaseTablePrefix) for each installation.

In order to keep the same look and feel across sites, you could designate one installation as your "master" installation and soft-link the html/ folder of other installations to the master, e.g.

/installation1/html - Master installation, work on template here
/installation2/html => soft link => /installation1/html
/installation3/html => soft link => /installation1/html

Similarly, you can use only the Automation Tool and /feeds/ folder of the master installation, soft-link the other installation's /feeds/ folders in the same way as it is likely that feeds will be relevant to more than one installation and this would avoid duplicating the feed across installations.

Cheers,
David.
--
PriceTapestry.com