[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]
