Thanks for your reply,
Just to be clear...performing my query without the order by clause will always return the list sorted by the primary identifier?
so that
SELECT SubmitId from BINDSubmit ORDER BY SubmitId == SELECT SubmitId from BINDSubmit
in this case
Marc
Dathan Pattishall wrote:
This tells the optimizer to do a table scan. If you used INNODB it's already sorted by the primary key since INNODB supports clustered indexes. Doing a table scan on innodb is very slow due to it's MVCC control.
It's going to take a long time.
DVP ---- Dathan Vance Pattishall http://www.friendster.com
-----Original Message-----
From: Marc Dumontier [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 02, 2005 12:02 PM
To: mysql@lists.mysql.com
Subject: performance on query with ORDER BY clause
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
