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.0000', `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.0000', `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 you post the query, and a 'SHOW CREATE TABLE [tablename]' for each table involved, someone maybe able to help you speed it up. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -----Original Message----- From: BD Sent: Friday, January 07, 2005 11:21 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? Hi Tom, OK thanks I just added the set-variable = key_buffer = 64M line to my my.cnf file and at least I got no errors and the MySQL server restarted OK and I got my test site running. 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 - that's just to bring up the front page of the store with the category - sub cat list. I'm sure there are lots of other switches in MySQl, do you have any other suggestions as to what I could do to reduce query times down to a reasonable 1-3 seconds? I just bought the book http://www.amazon.com/exec/obidos/tg/detail/-/0596003064/102-0076963-3409775 ?%5Fencoding=UTF8&v=glance but it has not arrived yet. Any suggestions in the mean time? BD -----Original Message----- From: Tom Crimmins Sent: Friday, January 07, 2005 12:08 PM To: BD Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? [snip] I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a "0 DB_ERROR_NOT_CONNECTED" error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I'm running MySQL 3.23.56 on a linux box. [/snip] Sorry, I didn't explain that you were probably not able to connect because prior to version 4.1 setting variable in my.cnf require that you add set-variable = before the setting ie. 'set-variable = key_buffer = 64M'. When you restarted mysql it probably exited on error. [snip] OK I tried this, I must be doing something wrong, I entered in: SET GLOBAL key_buffer_size = 64 * 1024 *1024; and got the error: "You have an error in your SQL syntax near 'key_buffer_size = 64 * 1024 *1024' at line 1" [/snip] Sorry about this too, in 3.23 leave out the word global. If you can restart without a problem though, I would jest add the 'set-variable = key_buffer = 64M' line to your my.cnf file and restart mysql. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -----Original Message----- From: BD Sent: Friday, January 07, 2005 9:07 AM To: mysql@lists.mysql.com Subject: Slow queries, need advice on how to improve; key_buffer? Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a "0 DB_ERROR_NOT_CONNECTED" error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command "set_variable=key_buffer=64M" (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
