Indexing cscart Database?

General General Questions
1

Does anyone know how to or can point me in the direction of how to index cs-cart's database?



Apparently I have a lot of slow queries and that I need to index the site. I just have literary no idea how too.



The tables showing up the query listed below are these:



[color=#000000][font=sans-serif][size=3]cscart_product_features_values[/size][/font][/color]

[color=#000000][font=sans-serif][size=3]products[/size][/font][/color]

[color=#000000][font=sans-serif][size=3]descr1[/size][/font][/color]

[color=#000000][font=sans-serif][size=3]prices[/size][/font][/color]

[color=#000000][font=sans-serif][size=3]prices_2[/size][/font][/color]

[color=#000000][font=sans-serif][size=3]products_categories[/size][/font][/color]

[color=#000000][font=sans-serif][size=3]cscart_categories[/size][/font][/color]

[color=#000000][font=sans-serif][size=3]cscart_seo_names[/size][/font][/color]



Here is 1 of my queries:




Thu Jan 31 11:00:35 2013

Query_time: 13.519528 Lock_time: 0.000525 Rows_sent: 48 Rows_examined: 169587

use harpermo_harper;

SET timestamp=1359655235;

SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price, descr1.short_description, IF(descr1.short_description = '', descr1.full_description, '') as full_description, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, products_categories.position, cscart_seo_names.name as seo_name, IF(products.age_verification = 'Y', 'Y', IF(cscart_categories.age_verification = 'Y', 'Y', cscart_categories.parent_age_verification)) as age_verification, IF(products.age_limit > cscart_categories.age_limit, IF(products.age_limit > cscart_categories.parent_age_limit, products.age_limit, cscart_categories.parent_age_limit), IF(cscart_categories.age_limit > cscart_categories.parent_age_limit, cscart_categories.age_limit, cscart_categories.parent_age_limit)) as age_limit FROM cscart_products as products LEFT JOIN cscart_product_features_values ON cscart_product_features_values.product_id = products.product_id AND cscart_product_features_values.lang_code = 'EN' LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'EN' LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN cscart_product_prices as prices_2 ON prices.product_id = prices_2.product_id AND prices_2.lower_limit = 1 AND prices_2.price < prices.price AND prices_2.usergroup_id IN (0, 0, 1) INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = 'p' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'EN' WHERE 1 AND products.company_id = 0 AND prices.price >= 51.00 AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) AND prices_2.price IS NULL GROUP BY products.product_id ORDER BY descr1.product asc LIMIT 10128, 48

2

Who told you that you need to index your database? Presumably your web host, common as muck answer for the English translation of “our hosting isn't good enough”. I must admit that's a laughable excuse. Indexing will help speed up finding data, but adding, updating and deleting from the database will be slower. What you really need is a half decent host, there are no two ways about this one I'm afraid.

3

Note that the number of JOINs is the greatest cost in retrieving data and that indexes themselves are the greatest cost in an update.

So when you over-index a database (or most frequently used tables) you need to understand the relationship of reads to writes for that data to determine where you want to pay the price. Nothing is free. Most likely this relationship will vary widely from site to site and feature by feature.



If your system is IO bound (most) then adding indexes is only going to add to your problem.



CS-cart uses accurate but sometimes complex queries to retrieve data for products and other complex objects. These can be costly.

However, one of my client runs over 225K (two hundred twenty five thousand) products without difficulty (usually). There are times when the DB backs up under load and long queries but it is quite infrequent.



It is most likely the INNER JOIN in your query that is taking the time. Generally an INNER JOIN is used to exclude all other rows if a match is NOT found. But this happens atter the outer joins (LEFT/RIGHT). within the DB engine.



Not sure I'd blame the host either. 169K rows is a lot of rows to return from a complex query such as this. Addons add to JOINs so it might be that you have an addon that is significantly impacting this query by adding the INNER JOIN or redundant LEFT JOINS. But the DB engine should catch any redundancies unless they occur after the OUTER JOINs.



Adding memory to mySQL (contact your host) is the BEST solution to long-query issues. Most long-queries are IO bound due to having to write/read data to/from disk in order to service the request. With enough memory, this isn't required.

4

Well yes the current host did say this.



I did go ahead and sign up with a new host. Future hosting is who I signed up with, with level 2 VPS server.



How about I get the transfer complete then we can retouch this subject,

Would that be better?

5

Get your site transferred, perform the Maintenance task on the database, set up caching, and then see if you have any more problems…