Does the mysql table type matters?

Question

I am new to mysql. Along create the project, I didnt pay attention to tables type and collation type. Now, I already have 20 tables, 15 tables are MyISAM type, the rest are innoDB type. I notice my application becomes very slow. Is it because of the table type?

More information:

The 15 tables collation is utf_unicode_ci, and the the other 5 are latin1_swedish_ci. (I am not sure why its different) Does it effect the performance?

My app is in Flash > php > MySQL.

The login, loading highscore and saving highscores is very very very slow now!!

Answer 1

The basic differente between MyISAM and InnoDB is that the later can use transactions for atomic changes to the database. MyISAM is usually faster for simpler tables but it shouldn't be a problem with your setup. Have you created the right indexes on the table to make updates faster?

Answer 2

Is it slow if you go direct to the MYSQL DB and run the query? (either from commandline mysql tools or using a GUI like SQLYog) Is it slow if you just use PHP->MySQL?

We may be trying to solve a MySQL problem when the DB is fine and the performance bottleneck lies elsewhere; from your description the query is very basic and should be quick even without optimisation.

Answer 3

There are definite pros and cons to each db type. The 'bragging rights of sorts for the InnoDB is that it supports row locking, as opposed to the entire table lock of the MyIsam. What does that mean for you? If you have a table that is read very often and is changed very often and the SQL statement uses a 'lock' command, the InnoDB will be slightly faster. As it only has to lock the row that is being manipulated as opposed to the entire table.

Other than that there really isn't to many difference between the two. It comes down to great d/b planning, making sure your tables are index, and making sure they are optimized.

[EDIT 2009-11-11]

After thinking about the problem a little more, I'm wondering if the problem is actually in the code. Databases are, very rarely, the root of the problem. Run some test queries from the gui/cli see how long an average query your program does takes. If the number is greater than half a second (.05), there is some room for optimization. If it is less than half a second your database is perfectly fine.

Once you optimize check the query times again. They will probably be even smaller now. I am willing to bet that the error is in the code.

Answer 4

Shouldn't matter, not for small scale databases anyway. I would recommend MyISAM as it is simpler to administer and takes up a little less memory. It doesn't have as many advanced features as InnoDB but with what you're doing it really doesn't matter.

I suspect that you have two problems that are not dependent on table type: the main one is indexing and the other is memory.

1) If you have a unique 'ID' column in a table make sure you index it and that it is set as "unique". Also, you should try to have indexes on any column that you use in a 'WHERE' clause. For example, if you search for a user name (e.g. SELECT * FROM userdetails WHERE username='harry';) then you should put a secondary index on the 'username' column.

2) Memory -- are you running on shared hosting or a VPS? it is possible that you're running out of RAM? Try going up to at least 256mb RAM.

Answer 5

Table type makes a huge difference in determining how the database will perform, particularly under heavy load with a lot of rows.

For example, MyISAM is generally faster than InnoDB, however, MyISAM has to lock the entire table when performing updates or deletes, or Inserts that don't get appended to the file, preventing all other operations (including Selects) until the operation finishes. InnoDB doesn't do table-level locking, so it can handle that scenario more efficiently.

InnoDB and MyISAM also implement indexing differently, so an index that makes a table more efficient for one of the types could make it actually run slower using the other table type.

There are too many differences to list here, and most of them take a while to explain, but I would suggest perusing the relevant sections from High Performance MySQL for more information.