What MySQL my.ini parameters should I change for a large table join and update?
I have the following:
- TableA - 20M rows
- TableB - 500K rows
And many queries, in particular the following, take forever.
UPDATE
TableA AS A
INNER JOIN
TableB AS B
ON B.Value IS NOT NULL AND A.Key=B.Key
SET
A.Value = B.Value
WHERE
A.Value IS NULL;
I know my MySQL (MyISAM) is not configured for large tables and I'm sure it could use more of the available memory (4GB total) or CPUs (i7). What parameters in my.ini should I be looking at?
开发者_运维问答I've started with key_buffer_size = 1536M because tableA has a 1gb index.
For innodb
innodb_buffer_pool_size
set to approx 80% of memory you want available to mysqlinnodb_log_file_size
*innodb_log_files_in_group
should be large enough that changes will be written to disk no more than once a second
But easier still use the configuration wizard https://tools.percona.com/wizard
精彩评论