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]



Reply via email to