MySQL Forums
Forum List  »  Performance

Re: Query Takes Longer Each Time Its Run
Posted by: anthony
Date: February 01, 2011 01:23PM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
4043
January 26, 2011 02:59PM
1554
January 27, 2011 02:35PM
1638
January 27, 2011 10:59PM
2078
January 28, 2011 01:38PM
1415
January 30, 2011 07:20PM
1477
January 31, 2011 01:22AM
1576
January 31, 2011 01:25AM
1422
February 01, 2011 12:38AM
1537
February 01, 2011 12:56AM
Re: Query Takes Longer Each Time Its Run
1485
February 01, 2011 01:23PM
1720
February 01, 2011 08:41PM
2228
February 03, 2011 02:04PM
1685
February 15, 2011 09:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.