I've recently upgraded from 2.2.5 to 4.3.4 and I have noticed a huge increase in joins without indexes. Is CSC aware of this? I thought CSC put a lot of time in optimization but apparently they skipped this part.
How would one go about figuring out how much join_buffer_size to add or is it just guess gradually until you get it right?
DB (query) tuning is an art in and of itself with specialized tools and specific domain expertise needed to do the job properly.
I don't think you can expect an index for every join condition but the vast majority of joins occur on primary keys.
The default for join_buffer_size is currently 128K. It used to be 8228K (8.035M) which is a huge difference. I went ahead and set it at 1M but from reading the mysql documentation, it may not do any good and adding indexes is preferred.
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_join_buffer_size
As usual, let us know what you learn. I'm guessing you used the defaults and that mySQL was upgraded and new defaults used.
128K seems rather small given that it's pretty easy for 2 rows to take up that space if it has 'text' columns (like the descriptions tables).
I'm guessing you used the defaults and that mySQL was upgraded and new defaults used.
I've been using mysql 5.5.4 for quite some time so I am pretty sure that join_buffer_size was 128K even when using 2.2.5.
It hasn't even been 24 hours and joins without indexes is already up to 1400. Before I changed from 128K to 1M, joins without indexes was 11000+ after 8 days so it doesn't look like 1M is doing any good. I am reluctant to set it at 8M because it is going to put memory usage for mysql at about 40%.
Isn't "joins without indexes" a counter? How would memory impact the number of joins without indexes?
I was wondering that myself. Suggestions state to either add indexes or add to the join_buffer_size if joins without indexes is high.
Makes sense to me. But adding the memory won't reduce the joins without indexes, it will only handle them better.
Okay, but on the same note, the suggestions are based on the counter so why even suggest adding to join_buffer_size if the result is not going to change? If you add 4GB to the join_buffer_size and the joins without indexes doesn't change, are you supposed to add even more? LMAO!
Basically the ultimate fix is to add indexes. I'm not holding my breath for CSC to do it. I wonder how many times they have read this thread to just ignore it?
Step back and think about how it works. Think of the join_buffer_size as a cache. Un-indexed join results are read into this buffer. If all of them can stay in the buffer then they are in memory and don't have to be read from disk. Otherwise if the buffer overflows then it will do new table scans to address the unindexed joins and (I'll guess) the results are read into the buffer with the JOIN clause as the key.
If you can identify the JOINs that are seen as unindexed and what object they are associated with, it might be easier to address. All the obvious things are well indexed like product/category descriptions, names, titles, SEO, etc. So it's probably more of the dynamic type date like product features, block content (filling) or possibly addons. Remember that addons use hooks to insert their data into things like product data, etc. if they've not done their tables (or JOIN clauses) correctly, then that could also be the cause.
Please publish data on the inindexed joins so we can at least identify the source of the problem(s).
Do you have any issues with MySQL or CS-Cart perfromance? Why even bother about join_buffer_size if not?
Do you have any issues with MySQL or CS-Cart perfromance? Why even bother about join_buffer_size if not?
I'm not sure. The site has only been live for a week or 2.
Being an automotive tech, this is what your questions sound like to me..."Do you have issues with the running/performance of your vehicle? Why even bother with the check engine light if not?"
I enabled log joins without indexes and this is what I have after about 12 hours:
Reading mysql slow query log from /var/lib/mysql/server-slow.log
Count: 2905 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (2905), 2users@localhost
SELECT * FROM cscart_bm_locations as l LEFT JOIN cscart_bm_locations_descriptions as d ON d.location_id = l.location_id AND d.lang_code = ‘S’ WHERE l.layout_id = N AND l.dispatch = ‘S’ AND (FIND_IN_SET(N, l.object_ids) OR l.object_ids = ‘S’) ORDER BY l.object_ids desc LIMIT N
Count: 2453 Time=0.03s (67s) Lock=0.00s (1s) Rows=1.0 (2452), 2users@localhost
SELECT a.row FROM (SELECT products.product_id, @r := @r + N as row FROM cscart_products as products LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ‘S’ LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = N 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 = ‘S’ OR FIND_IN_SET(N, cscart_categories.usergroup_ids) OR FIND_IN_SET(N, cscart_categories.usergroup_ids)) AND cscart_categories.status IN (‘S’, ‘S’) LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id AND shared_descr.company_id = N AND shared_descr.lang_code = ‘S’ LEFT JOIN cscart_supplier_links ON cscart_supplier_links.object_id = products.product_id AND cscart_supplier_links.object_type = ‘S’ WHERE N AND cscart_categories.category_id IN (N) AND cscart_categories.company_id = N AND (products.usergroup_ids = ‘S’ OR FIND_IN_SET(N, products.usergroup_ids) OR FIND_IN_SET(N, products.usergroup_ids)) AND products.status IN (‘S’) AND prices.usergroup_id IN (N, N, N) GROUP BY products.product_id ORDER BY descr1.product asc) AS a WHERE a.product_id = N
Count: 2398 Time=0.00s (1s) Lock=0.00s (0s) Rows=20.0 (47966), 2users@localhost
SELECT cscart_bm_snapping.grid_id as grid_id,cscart_bm_snapping.block_id as block_id,IFNULL(dynamic_object_content.content, default_content.content) as content,IFNULL(dynamic_object_content.object_id, default_content.object_id) AS object_id,IFNULL(dynamic_object_content.object_type, default_content.object_type) AS object_type,cscart_bm_block_statuses.object_ids as object_ids,cscart_bm_snapping.,cscart_bm_blocks.,cscart_bm_blocks_descriptions.* FROM cscart_bm_snapping LEFT JOIN cscart_bm_blocks ON cscart_bm_blocks.block_id = cscart_bm_snapping.block_id LEFT JOIN cscart_bm_block_statuses ON cscart_bm_snapping.snapping_id = cscart_bm_block_statuses.snapping_id AND cscart_bm_block_statuses.object_type LIKE ‘S’ LEFT JOIN cscart_bm_blocks_descriptions ON cscart_bm_blocks.block_id = cscart_bm_blocks_descriptions.block_id LEFT JOIN cscart_bm_blocks_content AS default_content ON cscart_bm_blocks.block_id = default_content.block_id AND cscart_bm_blocks_descriptions.lang_code = default_content.lang_code AND default_content.snapping_id = N AND default_content.object_id = N AND default_content.object_type like ‘S’ LEFT JOIN cscart_bm_blocks_content AS dynamic_object_content ON cscart_bm_blocks.block_id = dynamic_object_content.block_id AND cscart_bm_blocks_descriptions.lang_code = dynamic_object_content.lang_code AND dynamic_object_content.object_id = N AND dynamic_object_content.object_type like ‘S’ WHERE cscart_bm_snapping.grid_id IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N) AND cscart_bm_blocks_descriptions.lang_code=‘S’ AND cscart_bm_blocks.company_id = N ORDER BY cscart_bm_snapping.order, cscart_bm_snapping.block_id
Count: 2289 Time=0.03s (72s) Lock=0.00s (0s) Rows=9.3 (21189), 2users@localhost
SELECT cscart_suppliers.supplier_id, cscart_suppliers.name, CONCAT_WS(‘S’, cscart_suppliers.zipcode, IFNULL(cscart_country_descriptions.country, cscart_suppliers.country), IFNULL(cscart_state_descriptions.state, cscart_suppliers.state), cscart_suppliers.city, cscart_suppliers.address) as view_address, CONCAT_WS(‘S’, IFNULL(cscart_country_descriptions.country, cscart_suppliers.country), cscart_suppliers.city, cscart_suppliers.address) as api_address, cscart_product_suppliers_stock.amount, cscart_suppliers.coordinates FROM cscart_suppliers LEFT JOIN cscart_product_suppliers_stock ON (cscart_suppliers.supplier_id = cscart_product_suppliers_stock.supplier_id AND cscart_product_suppliers_stock.product_id = N) LEFT JOIN cscart_country_descriptions ON (cscart_suppliers.country = cscart_country_descriptions.code AND cscart_country_descriptions.lang_code = ‘S’) LEFT JOIN cscart_states ON (cscart_suppliers.state = cscart_states.code AND cscart_states.country_code = cscart_suppliers.country) LEFT JOIN cscart_state_descriptions ON (cscart_state_descriptions.state_id = cscart_states.state_id AND cscart_state_descriptions.lang_code = ‘S’) INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = cscart_suppliers.supplier_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE N
Count: 1136 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 2users@localhost
SELECT cscart_pages., cscart_page_descriptions.page, cscart_seo_names.name as seo_name, cscart_seo_names.path as seo_path FROM cscart_pages LEFT JOIN cscart_page_descriptions ON cscart_pages.page_id = cscart_page_descriptions.page_id AND cscart_page_descriptions.lang_code = ‘S’ LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = cscart_pages.page_id AND cscart_seo_names.type = ‘S’ AND cscart_seo_names.dispatch = ‘S’ AND cscart_seo_names.lang_code = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = cscart_pages.page_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE N AND cscart_pages.id_path LIKE ‘S’ AND cscart_pages.status IN (‘S’) AND cscart_pages.page_type IN (‘S’) AND (cscart_pages.usergroup_ids = ‘S’ OR FIND_IN_SET(N, cscart_pages.usergroup_ids) OR FIND_IN_SET(N, cscart_pages.usergroup_ids)) AND (use_avail_period = ‘S’ OR (use_avail_period = ‘S’ AND avail_from_timestamp <= N AND avail_till_timestamp >= N)) ORDER BY cscart_pages.timestamp desc LIMIT N, N
Count: 1095 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.0 (0), 2users@localhost
SELECT v.feature_id, v.value, v.value_int, v.variant_id, f.feature_type, fd.description, fd.prefix, fd.suffix, vd.variant, f.parent_id, ft.filter_id, ft.field_type, f.position, gf.position as gposition FROM cscart_product_features as f LEFT JOIN cscart_product_features_values as v ON v.feature_id = f.feature_id LEFT JOIN cscart_product_features_descriptions as fd ON fd.feature_id = v.feature_id AND fd.lang_code = ‘S’ LEFT JOIN cscart_product_feature_variants fv ON fv.variant_id = v.variant_id LEFT JOIN cscart_product_filters AS ft ON ft.feature_id = f.feature_id LEFT JOIN cscart_product_feature_variant_descriptions as vd ON vd.variant_id = fv.variant_id AND vd.lang_code = ‘S’ LEFT JOIN cscart_product_features as gf ON gf.feature_id = f.parent_id AND gf.feature_type = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = f.feature_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE f.status = ‘S’ AND IF(f.parent_id, (SELECT status FROM cscart_product_features as df WHERE df.feature_id = f.parent_id), ‘S’) = ‘S’ AND v.product_id = N AND f.display_on_header = ‘S’ AND ( f.categories_path = ‘S’ OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) ) AND (v.variant_id != N OR (f.feature_type != ‘S’ AND v.value != ‘S’) OR (f.feature_type = ‘S’) OR v.value_int != ‘S’) AND v.lang_code = ‘S’ ORDER BY fd.description, fv.position
Count: 986 Time=0.04s (38s) Lock=0.00s (0s) Rows=1.0 (972), user@localhost
SELECT product_id FROM cscart_products WHERE product_code = ‘S’ AND company_id = ‘S’
Count: 545 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.9 (501), user@localhost
SELECT cscart_discussion.object_id AS product_id, AVG(cscart_discussion_rating.rating_value) AS average_rating, cscart_discussion.type AS discussion_type, cscart_discussion.thread_id AS discussion_thread_id FROM cscart_discussion LEFT JOIN cscart_discussion_posts ON cscart_discussion_posts.thread_id = cscart_discussion.thread_id AND cscart_discussion_posts.status = “S” LEFT JOIN cscart_discussion_rating ON cscart_discussion.thread_id = cscart_discussion_rating.thread_id AND cscart_discussion_rating.post_id = cscart_discussion_posts.post_id AND cscart_discussion_rating.rating_value != N WHERE cscart_discussion.object_id IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N) AND cscart_discussion.object_type = “S” GROUP BY cscart_discussion.object_id
Count: 542 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), user@localhost
SELECT v.feature_id, v.value, v.value_int, v.variant_id, f.feature_type, fd.description, fd.prefix, fd.suffix, vd.variant, f.parent_id, ft.filter_id, ft.field_type, f.position, gf.position as gposition FROM cscart_product_features as f LEFT JOIN cscart_product_features_values as v ON v.feature_id = f.feature_id LEFT JOIN cscart_product_features_descriptions as fd ON fd.feature_id = v.feature_id AND fd.lang_code = ‘S’ LEFT JOIN cscart_product_feature_variants fv ON fv.variant_id = v.variant_id LEFT JOIN cscart_product_filters AS ft ON ft.feature_id = f.feature_id LEFT JOIN cscart_product_feature_variant_descriptions as vd ON vd.variant_id = fv.variant_id AND vd.lang_code = ‘S’ LEFT JOIN cscart_product_features as gf ON gf.feature_id = f.parent_id AND gf.feature_type = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = f.feature_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE f.status = ‘S’ AND IF(f.parent_id, (SELECT status FROM cscart_product_features as df WHERE df.feature_id = f.parent_id), ‘S’) = ‘S’ AND v.product_id = N AND f.display_on_header = ‘S’ AND ( f.categories_path = ‘S’ OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) ) AND (v.variant_id != N OR (f.feature_type != ‘S’ AND v.value != ‘S’) OR (f.feature_type = ‘S’) OR v.value_int != ‘S’) AND v.lang_code = ‘S’ ORDER BY fd.description, fv.position
Count: 538 Time=0.00s (0s) Lock=0.00s (0s) Rows=23.9 (12877), 2users@localhost
SELECT cscart_bm_snapping.grid_id as grid_id,cscart_bm_snapping.block_id as block_id,IFNULL(dynamic_object_content.content, default_content.content) as content,IFNULL(dynamic_object_content.object_id, default_content.object_id) AS object_id,IFNULL(dynamic_object_content.object_type, default_content.object_type) AS object_type,cscart_bm_block_statuses.object_ids as object_ids,cscart_bm_snapping.,cscart_bm_blocks.,cscart_bm_blocks_descriptions. FROM cscart_bm_snapping LEFT JOIN cscart_bm_blocks ON cscart_bm_blocks.block_id = cscart_bm_snapping.block_id LEFT JOIN cscart_bm_block_statuses ON cscart_bm_snapping.snapping_id = cscart_bm_block_statuses.snapping_id AND cscart_bm_block_statuses.object_type LIKE ‘S’ LEFT JOIN cscart_bm_blocks_descriptions ON cscart_bm_blocks.block_id = cscart_bm_blocks_descriptions.block_id LEFT JOIN cscart_bm_blocks_content AS default_content ON cscart_bm_blocks.block_id = default_content.block_id AND cscart_bm_blocks_descriptions.lang_code = default_content.lang_code AND default_content.snapping_id = N AND default_content.object_id = N AND default_content.object_type like ‘S’ LEFT JOIN cscart_bm_blocks_content AS dynamic_object_content ON cscart_bm_blocks.block_id = dynamic_object_content.block_id AND cscart_bm_blocks_descriptions.lang_code = dynamic_object_content.lang_code AND dynamic_object_content.object_id = N AND dynamic_object_content.object_type like ‘S’ WHERE cscart_bm_snapping.grid_id IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N) AND cscart_bm_blocks_descriptions.lang_code=‘S’ AND cscart_bm_blocks.company_id = N ORDER BY cscart_bm_snapping.order, cscart_bm_snapping.block_id
Count: 511 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 2users@localhost
SELECT pf.feature_id, pf.company_id, pf.feature_type, pf.parent_id, pf.display_on_product, pf.display_on_catalog, pf.display_on_header, cscart_product_features_descriptions.description, cscart_product_features_descriptions.lang_code, cscart_product_features_descriptions.prefix, cscart_product_features_descriptions.suffix, pf.categories_path, cscart_product_features_descriptions.full_description, pf.status, pf.comparison, pf.position FROM cscart_product_features AS pf LEFT JOIN cscart_product_features_descriptions ON cscart_product_features_descriptions.feature_id = pf.feature_id AND cscart_product_features_descriptions.lang_code = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = pf.feature_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE pf.feature_type = ‘S’ AND (pf.feature_id IN (‘S’) OR pf.feature_id NOT IN (SELECT parent_id FROM cscart_product_features)) AND pf.display_on_product = ‘S’ AND ( pf.categories_path = ‘S’ OR ISNULL(pf.categories_path)OR FIND_IN_SET(N, pf.categories_path) OR FIND_IN_SET(N, pf.categories_path) ) ORDER BY pf.position, cscart_product_features_descriptions.description
Count: 393 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 2users@localhost
SELECT v.feature_id, v.value, v.value_int, v.variant_id, f.feature_type, fd.description, fd.prefix, fd.suffix, vd.variant, f.parent_id, ft.filter_id, ft.field_type, f.position, gf.position as gposition FROM cscart_product_features as f LEFT JOIN cscart_product_features_values as v ON v.feature_id = f.feature_id LEFT JOIN cscart_product_features_descriptions as fd ON fd.feature_id = v.feature_id AND fd.lang_code = ‘S’ LEFT JOIN cscart_product_feature_variants fv ON fv.variant_id = v.variant_id LEFT JOIN cscart_product_filters AS ft ON ft.feature_id = f.feature_id LEFT JOIN cscart_product_feature_variant_descriptions as vd ON vd.variant_id = fv.variant_id AND vd.lang_code = ‘S’ LEFT JOIN cscart_product_features as gf ON gf.feature_id = f.parent_id AND gf.feature_type = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = f.feature_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE f.status = ‘S’ AND IF(f.parent_id, (SELECT status FROM cscart_product_features as df WHERE df.feature_id = f.parent_id), ‘S’) = ‘S’ AND v.product_id = N AND f.display_on_header = ‘S’ AND ( f.categories_path = ‘S’ OR FIND_IN_SET(N, f.categories_path) ) AND (v.variant_id != N OR (f.feature_type != ‘S’ AND v.value != ‘S’) OR (f.feature_type = ‘S’) OR v.value_int != ‘S’) AND v.lang_code = ‘S’ ORDER BY fd.description, fv.position
Count: 267 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), user@localhost
SELECT pf.feature_id, pf.company_id, pf.feature_type, pf.parent_id, pf.display_on_product, pf.display_on_catalog, pf.display_on_header, cscart_product_features_descriptions.description, cscart_product_features_descriptions.lang_code, cscart_product_features_descriptions.prefix, cscart_product_features_descriptions.suffix, pf.categories_path, cscart_product_features_descriptions.full_description, pf.status, pf.comparison, pf.position FROM cscart_product_features AS pf LEFT JOIN cscart_product_features_descriptions ON cscart_product_features_descriptions.feature_id = pf.feature_id AND cscart_product_features_descriptions.lang_code = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = pf.feature_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE pf.feature_type = ‘S’ AND (pf.feature_id IN (‘S’) OR pf.feature_id NOT IN (SELECT parent_id FROM cscart_product_features)) AND pf.display_on_product = ‘S’ AND ( pf.categories_path = ‘S’ OR ISNULL(pf.categories_path)OR FIND_IN_SET(N, pf.categories_path) OR FIND_IN_SET(N, pf.categories_path) OR FIND_IN_SET(N, pf.categories_path) ) ORDER BY pf.position, cscart_product_features_descriptions.description
Count: 207 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), user@localhost
SELECT v.feature_id, v.value, v.value_int, v.variant_id, f.feature_type, fd.description, fd.prefix, fd.suffix, vd.variant, f.parent_id, ft.filter_id, ft.field_type, f.position, gf.position as gposition FROM cscart_product_features as f LEFT JOIN cscart_product_features_values as v ON v.feature_id = f.feature_id LEFT JOIN cscart_product_features_descriptions as fd ON fd.feature_id = v.feature_id AND fd.lang_code = ‘S’ LEFT JOIN cscart_product_feature_variants fv ON fv.variant_id = v.variant_id LEFT JOIN cscart_product_filters AS ft ON ft.feature_id = f.feature_id LEFT JOIN cscart_product_feature_variant_descriptions as vd ON vd.variant_id = fv.variant_id AND vd.lang_code = ‘S’ LEFT JOIN cscart_product_features as gf ON gf.feature_id = f.parent_id AND gf.feature_type = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = f.feature_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE f.status = ‘S’ AND IF(f.parent_id, (SELECT status FROM cscart_product_features as df WHERE df.feature_id = f.parent_id), ‘S’) = ‘S’ AND v.product_id = N AND f.display_on_header = ‘S’ AND ( f.categories_path = ‘S’ OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) ) AND (v.variant_id != N OR (f.feature_type != ‘S’ AND v.value != ‘S’) OR (f.feature_type = ‘S’) OR v.value_int != ‘S’) AND v.lang_code = ‘S’ ORDER BY fd.description, fv.position
Count: 184 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), user@localhost
SELECT pf.feature_id, pf.company_id, pf.feature_type, pf.parent_id, pf.display_on_product, pf.display_on_catalog, pf.display_on_header, cscart_product_features_descriptions.description, cscart_product_features_descriptions.lang_code, cscart_product_features_descriptions.prefix, cscart_product_features_descriptions.suffix, pf.categories_path, cscart_product_features_descriptions.full_description, pf.status, pf.comparison, pf.position FROM cscart_product_features AS pf LEFT JOIN cscart_product_features_descriptions ON cscart_product_features_descriptions.feature_id = pf.feature_id AND cscart_product_features_descriptions.lang_code = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = pf.feature_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE pf.feature_type = ‘S’ AND (pf.feature_id IN (‘S’) OR pf.feature_id NOT IN (SELECT parent_id FROM cscart_product_features)) AND ( pf.categories_path = ‘S’ OR ISNULL(pf.categories_path)OR FIND_IN_SET(N, pf.categories_path) OR FIND_IN_SET(N, pf.categories_path) ) ORDER BY pf.position, cscart_product_features_descriptions.description
Count: 146 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), user@localhost
SELECT v.feature_id, v.value, v.value_int, v.variant_id, f.feature_type, fd.description, fd.prefix, fd.suffix, vd.variant, f.parent_id, ft.filter_id, ft.field_type, f.position, gf.position as gposition FROM cscart_product_features as f LEFT JOIN cscart_product_features_values as v ON v.feature_id = f.feature_id LEFT JOIN cscart_product_features_descriptions as fd ON fd.feature_id = v.feature_id AND fd.lang_code = ‘S’ LEFT JOIN cscart_product_feature_variants fv ON fv.variant_id = v.variant_id LEFT JOIN cscart_product_filters AS ft ON ft.feature_id = f.feature_id LEFT JOIN cscart_product_feature_variant_descriptions as vd ON vd.variant_id = fv.variant_id AND vd.lang_code = ‘S’ LEFT JOIN cscart_product_features as gf ON gf.feature_id = f.parent_id AND gf.feature_type = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = f.feature_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE f.status = ‘S’ AND IF(f.parent_id, (SELECT status FROM cscart_product_features as df WHERE df.feature_id = f.parent_id), ‘S’) = ‘S’ AND v.product_id = N AND f.display_on_header = ‘S’ AND ( f.categories_path = ‘S’ OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) OR FIND_IN_SET(N, f.categories_path) ) AND (v.variant_id != N OR (f.feature_type != ‘S’ AND v.value != ‘S’) OR (f.feature_type = ‘S’) OR v.value_int != ‘S’) AND v.lang_code = ‘S’ ORDER BY fd.description, fv.position
Count: 136 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (136), user@localhost
SELECT pfvd.variant_id, variant FROM cscart_product_feature_variant_descriptions AS pfvd LEFT JOIN cscart_product_feature_variants AS pfv ON pfv.variant_id = pfvd.variant_id WHERE feature_id = N AND variant IN (‘S’) AND lang_code = ‘S’
Count: 120 Time=2.07s (248s) Lock=0.00s (0s) Rows=10.0 (1200), user@localhost
SELECT SQL_CALC_FOUND_ROWS products.product_id, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) as product, popularity.total as popularity, cscart_supplier_links.supplier_id FROM cscart_products as products LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ‘S’ LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = N 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 = ‘S’ OR FIND_IN_SET(N, cscart_categories.usergroup_ids) OR FIND_IN_SET(N, cscart_categories.usergroup_ids)) AND cscart_categories.status IN (‘S’, ‘S’) LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id AND shared_descr.company_id = N AND shared_descr.lang_code = ‘S’ LEFT JOIN cscart_supplier_links ON cscart_supplier_links.object_id = products.product_id AND cscart_supplier_links.object_type = ‘S’ WHERE N AND cscart_categories.company_id = N AND popularity.total >= N AND (products.usergroup_ids = ‘S’ OR FIND_IN_SET(N, products.usergroup_ids) OR FIND_IN_SET(N, products.usergroup_ids)) AND products.status IN (‘S’) AND prices.usergroup_id IN (N, N, N) GROUP BY products.product_id ORDER BY popularity.total desc LIMIT N, N
Count: 116 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), user@localhost
SELECT pf.feature_id, pf.company_id, pf.feature_type, pf.parent_id, pf.display_on_product, pf.display_on_catalog, pf.display_on_header, cscart_product_features_descriptions.description, cscart_product_features_descriptions.lang_code, cscart_product_features_descriptions.prefix, cscart_product_features_descriptions.suffix, pf.categories_path, cscart_product_features_descriptions.full_description, pf.status, pf.comparison, pf.position FROM cscart_product_features AS pf LEFT JOIN cscart_product_features_descriptions ON cscart_product_features_descriptions.feature_id = pf.feature_id AND cscart_product_features_descriptions.lang_code = ‘S’ INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = pf.feature_id AND cscart_ult_objects_sharing.share_company_id = N AND cscart_ult_objects_sharing.share_object_type = ‘S’) WHERE pf.feature_type = ‘S’ AND (pf.feature_id IN (‘S’) OR pf.feature_id NOT IN (SELECT parent_id FROM cscart_product_features)) AND pf.display_on_product = ‘S’ AND ( pf.categories_path = ‘S’ OR ISNULL(pf.categories_path)OR FIND_IN_SET(N, pf.categories_path) OR FIND_IN_SET(N, pf.categories_path) OR FIND_IN_SET(N, pf.categories_path) OR FIND_IN_SET(N, pf.categories_path) ) ORDER BY pf.position, cscart_product_features_descriptions.description
Count: 111 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.4 (39), 2users@localhost
SELECT * FROM cscart_bm_locations as l LEFT JOIN cscart_bm_locations_descriptions as d ON d.location_id = l.location_id AND d.lang_code = ‘S’ WHERE l.layout_id = N AND l.dispatch = ‘S’ ORDER BY l.object_ids desc LIMIT N
So does anyone know how to identify what needs indexes? I tried to run the EXPLAIN statement on the queries but they return an out of sync, cannot run now error.
Consider "SELECT SQL_CALC_FOUND_ROWS products.product_id, ..." (as the largest). You can add indexes for all text fields. For example:
ALTER TABLE `cscart_product_descriptions` ADD INDEX ( `lang_code`, `product` ) ; ALTER TABLE `cscart_ult_product_descriptions` ADD INDEX ( `lang_code`, `product` ) ; ALTER TABLE `cscart_category_descriptions` ADD INDEX ( `lang_code`, `category` ) ; ...
If I'm not mistaken, you only need to index the columns that are used directly in the JOIN clause, not the fields returned. The JOIN should return the row so the SELECT can return the appropriate field.
Do remember that each index you add will impact the performance of all INSERT's and updates. So you really need to look at the overall performance of the system, not just of specific operations.
You might push on one side of the water balloon only to have it bulge on the other.
DB design is not an art. It is pure science and requires gobs of data and expertise to do properly.
The main idea of the indexes is to create a special list for the specified fields ( https://dev.mysql.com/doc/refman/5.5/en/create-index.html). It has no any connection to JOIN itself. But it will help to find appropriate lines.
Consider "SELECT SQL_CALC_FOUND_ROWS products.product_id, ..." (as the largest). You can add indexes for all text fields. For example:
ALTER TABLE `cscart_product_descriptions` ADD INDEX ( `lang_code`, `product` ) ; ALTER TABLE `cscart_ult_product_descriptions` ADD INDEX ( `lang_code`, `product` ) ; ALTER TABLE `cscart_category_descriptions` ADD INDEX ( `lang_code`, `category` ) ; ...
You're saying add indexes to all text type columns in the table or only the ones that show in the queries in the log?
Take for example the table cscart_product_descriptions. It has 12 columns and 11 of them are text.
[attachment=10380:cpd_table.jpg]
Another thing I don't understand is why the cscart_ult_... tables are being used if I only have one store.?
The JOIN should result in a row.
You only need to have indexes on what's being used in the JOIN itself to identify the rows specific to JOIN the data.
The columns (fields) used in the SELECT do NOT need to be indexed since there is no search for them AFTER the join returns the rows.
If there are other conditions to the JOIN beyond the primary keys used then those columns should be indexed. I.e. a:
LEFT JOIN ?:product_descriptions AS pd ON pd.product_id=?:products.product_id AND lang_code='en'.......
The lang_code column in ?:product_descriptions should also be indexed (and it is) to make the JOIN effecient (I.e. not require a full table scan to identify the row(s) of the JOIN).