Are multiple indexes on MYSQL table reason for slow UPDATES and INSERTS?

The performance of my (LAMP stack) site has degraded significantly over the last couple of days despite no code updates. It seems that only inserts, updates, and deletes of a particular MySQL table cause the problem. Any page that updates, inserts, or deletes an entry of the jobs "table" takes around 10 seconds to load. (E.G. UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)

SELECT queries seem to execute as before, though they seem to be slower if an update is taking place at the same time.

The table has around 180,000 entries. I noticed in the PHPMyAdmin view, that in addition to the "normal" index on the primary field, there is an index on the "entry_date" field (see image). Could that be an issue in this case? I've no idea why an index on that field would have been created.

If not, what else could be the source of the problem? I've checked space on the disk, which seems OK. (7 GB available) according to df.

``
SHOW CREATE TABLE job\G

Create Table: CREATE TABLE job (
job_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL DEFAULT '0',
entry_date date NOT NULL DEFAULT '0000-00-00',
timescale varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00',
title varchar(60) COLLATE latin1_german2_ci NOT NULL,
description text COLLATE latin1_german2_ci NOT NULL,
start_date varchar(60) COLLATE latin1_german2_ci NOT NULL,
address_town varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
address_county varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
postcode1 varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
postcode2 char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '',
status tinyint(4) NOT NULL DEFAULT '0',
cat_id int(4) NOT NULL DEFAULT '0',
price decimal(4,2) NOT NULL DEFAULT '1.00',
emailcount smallint(5) NOT NULL DEFAULT '-1',
emailcount2 int(11) NOT NULL DEFAULT '-1',
recemailcount` int

To answer the specific question:

• No, an extra index on (entry_date) or two would not hurt performance of updating a different, title column.

Additionally:

• No, the version of MySQL, 5.6, is not too ancient, even if some modern features (like window functions) are missing. You should have decent performance with decent hardware.

We can only speculate on the issue in hand but what could be wrong or explain it:

-
old, inefficient hardware: check the disk specifications, measure it's preformance.

-
fragmented index/table. Check MySQL docs and old questions/answers here of how to defragment MyISAM tables (OPTIMIZE TABLE).

Last but not least:

-
Your table is using MyISAM engine, which is old news. InnODB has replaced it as the default engine in MySQL years ago. There is no active development of this engine. It lacks several features compared to InnoDB (transactions, foreign key constraints, etc) and performance in most work loads. I strongly suggest you change your table (after testing of course that your apps and procedures do not break) to use InnoDB.

-
Importantly to your Question, InnoDB can perform SELECT and UPDATE at the same time (usually). MyISAM completely locks the table; the update or select must finish before the select or update can even start.

-
When switching from MyISAM to InnoDB, be sure to adjust key_buffer_size and innodb_buffer_pool_size.

StackExchange Database Administrators Q#281263, answer score: 6