Re: Sort Performance

[Jeff Hunter]

Here is the output from explain when using ignore index:

mysql> explain select security, cusip, intrate, duedate from security ignore index (security) where security like '%AEROPOST%';
+----------+------+---------------+------+---------+------+--------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+------+---------------+------+---------+------+--------+-------------+
| security | ALL | NULL | NULL | NULL | NULL | 514365 | Using where |
+----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select security, cusip, intrate, duedate from security ignore index (security) where security like '%AEROPOST%' order by security;
+----------+-------+---------------+----------+---------+------+--------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+-------+---------------+----------+---------+------+--------+-------------+
| security | index | NULL | security | 255 | NULL | 514365 | Using where |
+----------+-------+---------------+----------+---------+------+--------+-------------+
1 row in set (0.00 sec)

As far as the buffer size goes, innodb_buffer_pool_size is currently set at 128MB (25% of total memory on the box where this is running; swap usage is at 1%). Here are the current innodb configuration values:

mysql> show variables like 'innodb%';
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| innodb_additional_mem_pool_size | 10485760 |
| innodb_buffer_pool_size | 134217728 |
| innodb_data_file_path | invdb1:100M:autoextend |
| innodb_data_home_dir | /invdb/db |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | /invdb/db |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 33554432 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /invdb/db |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
+---------------------------------+------------------------+
19 rows in set (0.00 sec)