On Mon, Feb 04, 2002 at 05:41:46PM -0000, Robin Keech wrote: > (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
On what operating system? > 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??? Correct. And that key_buffer won't help much. How many of the rows actually match your query? > Can anyone explain this behaviour, and suggest a solution? MySQL will decide not to use an index in cases when it believes there is a faster way, such as a full table scan. This is often the case when a significant percentage of the rows are likely to match. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 0 days, processed 22,771,714 queries (385/sec. avg) --------------------------------------------------------------------- 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
