> So, I am puzzled at the "3 days".
Well, same for me. I am apologizing for not retesting this before asking in this forum. To be honest I have tried to delete the records nearly one year ago but I didn't thought about this issue further. A few days ago I had the same problem and I considered to ask for help. So I did but I forgot that I had a major hardware upgrade between than and now. However I swear it taked more than 3 days. Doing the same query now takes 3 minutes :( 3 minutes is also not as good as I would imagine but it is acceptable.
I really don't know what happend. I will take care of the DELETE statements over the next months and would like to come back if something goes wrong. But anyway I have learned something out of this and I would be glad if you could have a look at my INNODB settings.
regards
Daniel
> > DELETE FROM package WHERE packageid = 7749
>
> Since the index is UNIQUE, this is necessarily a
> 1-row delete. However, because of the FK
> relationship, will it be CASCADEing into `file`
> and deleting lots of rows there? Hundreds,
> sometimes thousands?
1 till 20,000 is possible
> > AUTO_INCREMENT=2084920112
>
> You are halfway to running out of ids!
Uh, never thought about this. All my other tables are much smaller than this one. Thanks for the notice.
> > KEY `name` (`name`,`path`),
> > KEY `name_2` (`name`),
>
> The second one is redundant and can be DROPped.
> However, if the table is huge, that will take a
> long time.
Did this change? May years ago? I have learned that it is best to create a KEY with the same columns as the WHERE statement. But indeed the EXPLAIN of a SELECT with a FORCE KEY of name/name_2 told me that there is no difference :)
> SHOW VARIABLES LIKE 'innodb%';
> There are several settings that _could_ be
> contributing the the issue.
mysql> SHOW VARIABLES LIKE 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 2097152 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 16777216 |
| 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_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| 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_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_legacy_cardinality_algorithm | ON |
+-----------------------------------------+------------------------+