2 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.

I wrote about pros and cons of using MyISAM vs InnoDB

With regard to your current question, here is a possible scenario:

  • article and article_comments are both MyISAM tables
  • article_comments has one or more indexes with status as a column
  • Index page updates for article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
  • You have SELECT queries that perform JOINs between article and article_comments

In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)

The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.

I wrote about pros and cons of using MyISAM vs InnoDB

With regard to your current question, here is a possible scenario:

  • article and article_comments are both MyISAM tables
  • article_comments has one or more indexes with status as a column
  • Index page updates for article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
  • You have SELECT queries that perform JOINs between article and article_comments

In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)

The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.

I wrote about pros and cons of using MyISAM vs InnoDB

With regard to your current question, here is a possible scenario:

  • article and article_comments are both MyISAM tables
  • article_comments has one or more indexes with status as a column
  • Index page updates for article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
  • You have SELECT queries that perform JOINs between article and article_comments

In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)

1
source | link

The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.

I wrote about pros and cons of using MyISAM vs InnoDB

With regard to your current question, here is a possible scenario:

  • article and article_comments are both MyISAM tables
  • article_comments has one or more indexes with status as a column
  • Index page updates for article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
  • You have SELECT queries that perform JOINs between article and article_comments

In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)