RE: Slow queries, need advice on how to improve; key_buffer - zen-cart?
OK thanks Tom, The application I am using for the site is www.zen-cart.com so I'm not sure I can do anything about changing the table indexes because it is a pre written php-MySQL open source freeware script. I'm getting the query times directly from the application and not MySQL. However I could post a query I generate directly from MySQL, how could I do that? What would be the command I should use to generate query times from MySQL? For the SHOW CREATE TABLE, I have posted below the five tables that (I believe) are directly involved in generating a product and category list on the front page of my test store. I did not post all tables in the database because there are 97 tables total in the database, but I think these are the pertinent tables involved in slow query times; any suggestions that I get here I will definitely pass along to the zen cart developers. If there is something that I can do without changing the PHP code of the application and fix just with MySQL settings that would be great... zen_products |CREATE TABLE `zen_products` ( `products_id` int(11) NOT NULL auto_increment, `products_type` int(11) NOT NULL default '1', `products_quantity` float NOT NULL default '0', `products_model` varchar(32) default NULL, `products_image` varchar(64) default NULL, `products_price` decimal(15,4) NOT NULL default '0.', `products_virtual` tinyint(1) NOT NULL default '0', `products_date_added` datetime NOT NULL default '0001-01-01 00:00:00', `products_last_modified` datetime default NULL, `products_date_available` datetime default NULL, `products_weight` decimal(5,2) NOT NULL default '0.00', `products_status` tinyint(1) NOT NULL default '0', `products_tax_class_id` int(11) NOT NULL default '0', `manufacturers_id` int(11) default NULL, `products_ordered` float NOT NULL default '0', `products_quantity_order_min` float NOT NULL default '1', `products_quantity_order_units` float NOT NULL default '1', `products_priced_by_attribute` tinyint(1) NOT NULL default '0', `product_is_free` tinyint(1) NOT NULL default '0', `product_is_call` tinyint(1) NOT NULL default '0', `products_quantity_mixed` tinyint(1) NOT NULL default '0', `product_is_always_free_shipping` tinyint(1) NOT NULL default '0', `products_qty_box_status` tinyint(1) NOT NULL default '1', `products_quantity_order_max` float NOT NULL default '0', `products_sort_order` int(11) NOT NULL default '0', `products_discount_type` tinyint(1) NOT NULL default '0', `products_discount_type_from` tinyint(1) NOT NULL default '0', `products_price_sorter` decimal(15,4) NOT NULL default '0.', `master_categories_id` int(11) NOT NULL default '0', `products_mixed_discount_quantity` tinyint(1) NOT NULL default '1', PRIMARY KEY (`products_id`), KEY `idx_products_date_added` (`products_date_added`) ) TYPE=MyISAM | | zen_categories_description | CREATE TABLE `zen_categories_description` ( `categories_id` int(11) NOT NULL default '0', `language_id` int(11) NOT NULL default '1', `categories_name` varchar(32) NOT NULL default '', `categories_description` text NOT NULL, PRIMARY KEY (`categories_id`,`language_id`), KEY `idx_categories_name` (`categories_name`) ) TYPE=MyISAM | zen_categories | CREATE TABLE `zen_categories` ( `categories_id` int(11) NOT NULL auto_increment, `categories_image` varchar(64) default NULL, `parent_id` int(11) NOT NULL default '0', `sort_order` int(3) default NULL, `date_added` datetime default NULL, `last_modified` datetime default NULL, `categories_status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`categories_id`), KEY `idx_categories_parent_id` (`parent_id`), KEY `idx_sort_order` (`sort_order`) ) TYPE=MyISAM | | zen_products_description | CREATE TABLE `zen_products_description` ( `products_id` int(11) NOT NULL auto_increment, `language_id` int(11) NOT NULL default '1', `products_name` varchar(64) NOT NULL default '', `products_description` text, `products_url` varchar(255) default NULL, `products_viewed` int(5) default '0', PRIMARY KEY (`products_id`,`language_id`), KEY `products_name` (`products_name`) ) TYPE=MyISAM | | zen_products_to_categories | CREATE TABLE `zen_products_to_categories` ( `products_id` int(11) NOT NULL default '0', `categories_id` int(11) NOT NULL default '0', PRIMARY KEY (`products_id`,`categories_id`) ) TYPE=MyISAM | +- -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Friday, January 07, 2005 12:23 PM To: BD Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? [snip] The problem now is, this did not do anything to improve the query and parse times. I'm testing out an on line store which has about 12,000 product entries in it and about 300 product categories. Both the parse and query times are running over 12 seconds [/snip] This is probably related to not having proper indexes on your tables. If
RE: Slow queries, need advice on how to improve; key_buffer - zen -cart?
[snip] The application I am using for the site is www.zen-cart.com so I'm not sure I can do anything about changing the table indexes because it is a pre written php-MySQL open source freeware script. I'm getting the query times directly from the application and not MySQL. [/snip] You could turn on logging in mysql to see what the query is that is taking so long, then make sure the tables are properly indexed based on this. I would hope that this software has properly indexed the tables, but you can verify this on your own. Add the following to your my.cnf to enable the logging of slow queries. [mysqld] set-variable = long_query_time=2 log-long-format log-slow-queries = /var/log/mysqld.slow.log (or whatever file you want, just make sure the user mysqld is running as has write permissions to it.) --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 11:58 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer - zen-cart? OK thanks Tom, The application I am using for the site is www.zen-cart.com so I'm not sure I can do anything about changing the table indexes because it is a pre written php-MySQL open source freeware script. I'm getting the query times directly from the application and not MySQL. However I could post a query I generate directly from MySQL, how could I do that? What would be the command I should use to generate query times from MySQL? For the SHOW CREATE TABLE, I have posted below the five tables that (I believe) are directly involved in generating a product and category list on the front page of my test store. I did not post all tables in the database because there are 97 tables total in the database, but I think these are the pertinent tables involved in slow query times; any suggestions that I get here I will definitely pass along to the zen cart developers. If there is something that I can do without changing the PHP code of the application and fix just with MySQL settings that would be great... zen_products |CREATE TABLE `zen_products` ( `products_id` int(11) NOT NULL auto_increment, `products_type` int(11) NOT NULL default '1', `products_quantity` float NOT NULL default '0', `products_model` varchar(32) default NULL, `products_image` varchar(64) default NULL, `products_price` decimal(15,4) NOT NULL default '0.', `products_virtual` tinyint(1) NOT NULL default '0', `products_date_added` datetime NOT NULL default '0001-01-01 00:00:00', `products_last_modified` datetime default NULL, `products_date_available` datetime default NULL, `products_weight` decimal(5,2) NOT NULL default '0.00', `products_status` tinyint(1) NOT NULL default '0', `products_tax_class_id` int(11) NOT NULL default '0', `manufacturers_id` int(11) default NULL, `products_ordered` float NOT NULL default '0', `products_quantity_order_min` float NOT NULL default '1', `products_quantity_order_units` float NOT NULL default '1', `products_priced_by_attribute` tinyint(1) NOT NULL default '0', `product_is_free` tinyint(1) NOT NULL default '0', `product_is_call` tinyint(1) NOT NULL default '0', `products_quantity_mixed` tinyint(1) NOT NULL default '0', `product_is_always_free_shipping` tinyint(1) NOT NULL default '0', `products_qty_box_status` tinyint(1) NOT NULL default '1', `products_quantity_order_max` float NOT NULL default '0', `products_sort_order` int(11) NOT NULL default '0', `products_discount_type` tinyint(1) NOT NULL default '0', `products_discount_type_from` tinyint(1) NOT NULL default '0', `products_price_sorter` decimal(15,4) NOT NULL default '0.', `master_categories_id` int(11) NOT NULL default '0', `products_mixed_discount_quantity` tinyint(1) NOT NULL default '1', PRIMARY KEY (`products_id`), KEY `idx_products_date_added` (`products_date_added`) ) TYPE=MyISAM | | zen_categories_description | CREATE TABLE `zen_categories_description` | ( `categories_id` int(11) NOT NULL default '0', `language_id` int(11) NOT NULL default '1', `categories_name` varchar(32) NOT NULL default '', `categories_description` text NOT NULL, PRIMARY KEY (`categories_id`,`language_id`), KEY `idx_categories_name` (`categories_name`) ) TYPE=MyISAM | zen_categories | CREATE TABLE `zen_categories` ( `categories_id` int(11) NOT NULL auto_increment, `categories_image` varchar(64) default NULL, `parent_id` int(11) NOT NULL default '0', `sort_order` int(3) default NULL, `date_added` datetime default NULL, `last_modified` datetime default NULL, `categories_status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`categories_id`), KEY `idx_categories_parent_id` (`parent_id`), KEY `idx_sort_order` (`sort_order`) ) TYPE=MyISAM | | zen_products_description | CREATE TABLE `zen_products_description` ( `products_id` int(11) NOT NULL auto_increment, `language_id` int(11) NOT NULL default '1
RE: Slow queries, need advice on how to improve; key_buffer - zen -cart?
[snip] # Time: 050107 17:40:41 # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 13 Lock_time: 0 Rows_sent: 148 Rows_examined: 1567270 use zencarttest; select distinct m.manufacturers_id, m.manufacturers_name from zen_manufacturers m left join zen_products p on m.manufacturers_id = p.manufacturers_id where m.manufacturers_id = p.manufacturers_id and p.products_status= '1' order by manufacturers_name; [/snip] This appears to be the problem query. Looks like zen_products could use an index on (manufacturers_id, products_status), and zen_manufacturers could use an index on (manufacturers_id,manufacturers_name). You can try to add these indexes and run the query to see if it helps. You may want to do an EXPLAIN after adding the indexes to make see if it is using them. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
