How to improve performance of INSERT/UPDATE queries in MyISAM

Question

I am working with MySQL database and it has two MyISAM tables 1. Master (inf_article_details) table having 10+ million records and 2. Temporary (temp_inf_article_details) table having around 50,000+ records. These tables structure are same and they have only one column (BIGINT as datatype) as primary key. Data is filled into temporary table from different data source providers on a periodic basis and i have a scheduled job which pushes data from temporary table into master table.

FYI, master table is partitioned on KEY into 20 partitions

Index info of master table (inf_articles_details) as below:

show indexes from inf_articles_details;

Index info of temporary table (temp_inf_articles_details) as below:

show indexes from temp_inf_articles_details;

Explain result of master table as below:

explain select * from inf_articles_details

Explain result of temporary table as below:

explain select * from temp_inf_articles_details

Master table (inf_articles_details) structure (there are around 70+ columns in total)

CREATE TABLE inf_articles_details (
ard_ean_code bigint(20) unsigned NOT NULL,
ard_provider_reference bigint(20) unsigned DEFAULT NULL,
ard_reference varchar(20) NOT NULL,
ard_modified_date datetime DEFAULT NULL,
PRIMARY KEY (ard_ean_code),
KEY idx_ard_modified_date (ard_modified_date)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 /*!50100 PARTITION BY KEY () PARTITIONS 20 */

Temporary table (temp_inf_articles_details) structure is (there are around 70+ columns)

CREATE TABLE temp_inf_articles_details (
tard_ean_code bigint(20) unsigned NOT NULL,
tard_provider_reference bigint(20) unsigned DEFAULT NULL,
tard_status tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (tard_ean_code),
KEY idx_status (tard_status)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

as part of data updates task, we join these two tables on "ard_ean_code & tard_ean_code" and update the existing rows in master table with values from temporary table and insert the new rows into master from temporary table. But this data updates is taking too much time to insert/update data from temporary table to master table.

Is there any tuning i need to do to improve the performance?

Thanks.

Answer 1

MyISAM supports concurrent inserts: If a table has no free blocks in the middle of the data file, you can INSERT new rows into it at the same time that other threads are reading from the table.

Ref: Bulk Data for MyISAM Tables

Concurent Inserts

Hope it helps in some way