I have mySQL v3.23 running on a Mandrake 8.1 box (AMD 1600XP, 512MB RAM). The problem is that the main query (listed below) takes approximately 3 hours to run. my.cnf looks like: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking set-variable = key_buffer=128M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer+size=64M set-variable = thread_cache=8 set-variable = net_buffer_length=8K log-bin server-id = 1 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=64M set-variable = sort_buffer=64M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=64M set-variable = sort_buffer=64M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout We're using MyISAM tables. The table structure is this: tblMain - oldrank int(11) primary key username varchar(255) binary wucount bigint lastupdate datetime tblNew - uID int(11) auto_increment primary key username varchar(255) binary wucount bigint lastupdate datetime We have an index on tblMain.username. The query is this: SELECT SQL_BIG_RESULT tblNew.username, (tblNew.wucount - tblMain.wucount), tblNew.wucount, tblMain.wucount FROM tblNew LEFT OUTER JOIN tblMain ON (DATE_SUB(tblNew.lastupdate, INTERVAL $hourssinceNoon HOUR) = tblMain.lastupdate) ORDER BY tblNew.wucount DESC Where $hourssinceNoon is the number of hours since noon yesterday. tblNew typically has approximately 4500 records in it. tblMain has records added every 4 hours, which is all the records from tblNew. I was hoping anyone could help me out with some optimizations that could be done. We've already run optimize table tblMain, and that gave us almost an hours worth of performance gain. The output of SHOW STATUS is printed below as well. Any optimization ideas/techniques will be appreciated... +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | Aborted_clients | 1 | | Aborted_connects | 0 | | Bytes_received | 7277626 | | Bytes_sent | 1769127 | | Connections | 18 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 178 | | Created_tmp_files | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 0 | | Handler_read_first | 8640 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 13544 | | Handler_read_rnd_next | 2759592937 | | Handler_update | 0 | | Handler_write | 89261 | | Key_blocks_used | 773 | | Key_read_requests | 177906 | | Key_reads | 344 | | Key_write_requests | 50117 | | Key_writes | 36335 | | Max_used_connections | 1 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 3 | | Open_files | 7 | | Open_streams | 0 | | Opened_tables | 30 | | Questions | 44708 | | Select_full_join | 356 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 367 | | Slave_running | OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 355 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 13544 | | Sort_scan | 178 | | Table_locks_immediate | 53651 | | Table_locks_waited | 1 | | Threads_cached | 0 | | Threads_created | 2 | | Threads_connected | 2 | | Threads_running | 2 | | Uptime | 119195 | +--------------------------+------------+ 54 rows in set (0.00 sec) Any ideas?
---------------------------- This communication is confidential and may be legally privileged. If you are not the intended recipient, (i) please do not read or disclose to others, (ii) please notify the sender by reply mail, and (iii) please delete this communication from your system. Failure to follow this process may be unlawful. Thank you for your cooperation. ---------------------------- --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
