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]
