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)
Subject
Views
Written By
Posted
4043
January 26, 2011 02:59PM
1569
January 27, 2011 02:04PM
1554
January 27, 2011 02:35PM
1555
January 27, 2011 10:35PM
1638
January 27, 2011 10:59PM
1536
January 28, 2011 10:02AM
2078
January 28, 2011 01:38PM
1466
January 29, 2011 01:18AM
1415
January 30, 2011 07:20PM
1278
January 30, 2011 11:02PM
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.