Hi,
I have a simple query with an ORDER BY clause, and it's taking forever to run on this table. I hope i've included all relevent information...it might just be one of the4 server variables which need adjustment.
the query is
SELECT SubmitId from BINDSubmit ORDER BY SubmitId
SubmitId is the primary Key, about 150,000 records table type is INNODB
mysql> describe BINDSubmit;
+-----------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------------------+----------------+
| SubmitId | int(10) unsigned | | PRI | NULL | auto_increment |
| BindId | int(10) unsigned | | MUL | 0 | |
| UserId | int(10) unsigned | | MUL | 0 | |
| Delegate | int(10) unsigned | | MUL | 0 | |
| Visible | tinyint(1) | | | 1 | |
| Private | tinyint(1) | | | 0 | |
| Compressed | tinyint(1) | | | 0 | |
| Verified | tinyint(1) | | | 0 | |
| Status | tinyint(3) unsigned | | MUL | 0 | |
| CurationType | tinyint(3) unsigned | | | 1 | |
| RecordType | tinyint(3) unsigned | | MUL | 0 | |
| DateCreated | datetime | | MUL | 0000-00-00 00:00:00 | |
| DateLastRevised | datetime | | MUL | 0000-00-00 00:00:00 | |
| XMLRecord | longblob | | | | |
+-----------------+---------------------+------+-----+---------------------+----------------+
14 rows in set (0.00 sec)
mysql> select count(*) from BINDSubmit; +----------+ | count(*) | +----------+ | 144140 | +----------+ 1 row in set (5.09 sec)
mysql> explain select SubmitId from BINDSubmit ORDER BY SubmitId;
+------------+-------+---------------+---------+---------+------+--------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+------------+-------+---------------+---------+---------+------+--------+-------------+
| BINDSubmit | index | NULL | PRIMARY | 4 | NULL | 404947 | Using index |
+------------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
# The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 128M max_allowed_packet = 40M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4
# Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:100M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50
Any help would be appreciated, so far query has been running for 3000 seconds
Marc Dumontier
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
