(Please reply directly, I am only on the digest list, thanks) I have an SQL statement that is taking far too long to run, around 11 minutes (while locking inserts/updates).
We have installed some more RAM, and I need help with my.cnf and the troublesome SQL. Can anyone suggest some config values to best utilise the extra RAM I have just fitted? We have gone from 2G to 4G The table in question is very large, ie e3_id_chain.MYD = 3.7G ~ 53 million records e3_id_chain.MYI = 3.2G These are my current settings.... # The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking set-variable = key_buffer=512M set-variable = max_allowed_packet=2M set-variable = max_connections=500 set-variable = table_cache=512 set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=8 set-variable = wait_timeout=1800 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=4 set-variable = myisam_sort_buffer_size=64M log-bin low-priority-update When doing an SQL select it takes 11 minutes mysql> explain SELECT e3_id, prev_e3_id, log_id FROM e3_id_chain WHERE prev_e3_id IS NOT NULL AND log_id IS NOT NULL; +-------------+------+---------------------------+------+---------+------+-- --------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+------+---------------------------+------+---------+------+-- --------+------------+ | e3_id_chain | ALL | prev_e3_id_idx,log_id_idx | NULL | NULL | NULL | 58179805 | where used | +-------------+------+---------------------------+------+---------+------+-- --------+------------+ 1 row in set (0.00 sec) But its not using the index??? mysql> desc e3_id_chain; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | e3_id | varchar(80) | | MUL | | | | prev_e3_id | varchar(80) | YES | MUL | NULL | | | log_id | int(10) unsigned | YES | MUL | NULL | | +------------+------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) even when trying to force it to use an index, it still looks like it doesn't .... mysql> explain SELECT e3_id, prev_e3_id, log_id FROM e3_id_chain USE INDEX (log_id_idx) WHERE prev_e3_id IS NOT NULL AND log_id IS NOT NULL; +-------------+------+---------------------------+------+---------+------+-- --------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+------+---------------------------+------+---------+------+-- --------+------------+ | e3_id_chain | ALL | prev_e3_id_idx,log_id_idx | NULL | NULL | NULL | 58179810 | where used | +-------------+------+---------------------------+------+---------+------+-- --------+------------+ 1 row in set (0.00 sec) Can anyone explain this behaviour, and suggest a solution? Thanks Robin --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.313 / Virus Database: 174 - Release Date: 02/01/02 --------------------------------------------------------------------- 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
