optimize mysql database - myISAM db

Question

i have two question about msyql performance with my db using MyISAM engine :

1) what is smart way to solve the problem that when INSERT or UPDATE some rows in one table, many SELECT queries be hang on.

2) Is this easy to change from MyISAM to InnoDB with database is current running?

3) Why myISAM is still be default option of mySQL when InnoDB is good because it provide row-level locking?

4) I have one problem when design a database with view of post like below :

• I have one table have many "posts".

• I want to give one "post" a view number and this increase every time people view the post on the website.

• So, if i put the "view" field on the table "Posts" this will run the query "Update Posts set view=view+1" anytime visitor visit this post. This make other select query on this Row hang on.

• If i put the "view" field on other TABLE, i still get this problem because when i display a post on website i still need the view number for this post by using a inner join query. And this query still stuck if have update view query run.

Sorry for my bad English.

Answer 1

You already know the anwsers to almost all you questions, Switch to InnoDB

First check if you got InnoDB support. On your mysql server Execute the following query:

SHOW VARIABLES LIKE 'have_innodb';

if you get a value of 'YES' you can change every table you want to change with the following SQL Query:

ALTER TABLE [tablename] ENGINE=InnoDB;

(make a backup first)

In future releases MySQL will switch to InnoDB as default storage engine. But because of legacy setup this haven't been changed

Answer 2

Jaydee's suggestion of a mix of MyISAM and InnoDB is a good one. You can make the "views" table an InnoDB table and it shouldn't block reads during writes.

Alternatively, you could create a copy of of your main table in InnoDB, synchronize them, (triggers, two writes, whatever is necessary), and then switch them out. This will cause less down time than an ALTER TABLE..., but involves more work.