Re: Query Takes Longer Each Time Its Run

[anthony]

Here is all the info you asked for. Sorry about the UDF vs Stored Function, I know better, and I'll correct that. As for the indexes on the table, the original table is about 20 columns. I copied it with CREATE TABLE LIKE and then started dropping columns as I was testing it to get to the smallest test case. I forgot to remove them before I dumped the test db.

Not sure what this refers to: Do you have 15M rows in profile_thumbs_name?

Should I go ahead and post this as a bug?

mysql> SHOW VARIABLES LIKE '%buffer_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608    |
| innodb_log_buffer_size  | 8388608    |
| join_buffer_size        | 131072     |
| key_buffer_size         | 4294967296 |
| myisam_sort_buffer_size | 8388608    |
| preload_buffer_size     | 32768      |
| read_buffer_size        | 16777216   |
| read_rnd_buffer_size    | 262144     |
| sort_buffer_size        | 16777216   |
+-------------------------+------------+

mysql> SHOW TABLE STATUS LIKE 'records'\G
*************************** 1. row ***************************
           Name: records
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 626
 Avg_row_length: 17
    Data_length: 10642
Max_data_length: 4785074604081151
   Index_length: 9216
      Data_free: 0
 Auto_increment: 3911876
    Create_time: 2011-02-01 19:02:30
    Update_time: 2011-02-01 19:02:30
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

mysql> SHOW CREATE TABLE records\G
*************************** 1. row ***************************
       Table: records
Create Table: CREATE TABLE `records` (
  `RecordID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `UserID` int(11) unsigned NOT NULL DEFAULT '0',
  `ClockIn` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`RecordID`)
) ENGINE=MyISAM AUTO_INCREMENT=3911876 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE '%innodb%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    |
| ignore_builtin_innodb           | OFF                    |
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 8388608                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 1                      |
| innodb_buffer_pool_size         | 5242880                |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | ON                     |
| innodb_file_format_max          | Antelope               |
| innodb_file_per_table           | OFF                    |
| innodb_flush_log_at_trx_commit  | 0                      |
| innodb_flush_method             | O_DIRECT               |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 104857600              |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 90                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 0                      |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 8                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | ON                     |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 1.1.4                  |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+

mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.01 sec)