Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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!!

share|improve this question
    
Post your queries & related table structures onto the sister site: StackOverflow.com for feedback on performance tuning. The collation shouldn't matter... –  OMG Ponies Nov 11 '09 at 5:34

5 Answers 5

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?

share|improve this answer
    
I am not sure how to create a good index. Normally, the user_id and username would be my indeces. –  mysql_learner Nov 11 '09 at 3:56
    
An index should (where possible) be a number. That is easier to index and sort as it is a data type that the processor can deal with intrinsically. –  sybreon Nov 11 '09 at 6:37

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.

share|improve this answer
    
Flash call loadscore.php, php return data in xml format. I used firebug, it shows 550ms to call the loadscores.php. Are you saying that, the displaying, Flash convert xml data to display the highscore board is slow? Not mysql problem? –  mysql_learner Nov 11 '09 at 4:37

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.

share|improve this answer
    
if my query is like this: SELECT gameid, score FROM users WHERE game_id = 1 ORDER BY score DESC LIMIT 10; I set game_id as the index. Am i doing correct? –  mysql_learner Nov 11 '09 at 4:13
    
Sorry "SELECT FROM highscores" not "users". BTW, I already optimized the highscores table, add index to game_id and restart mysql server. I didnt see much improvement. What else can I do to improve the speed? –  mysql_learner Nov 11 '09 at 4:17
    
how many records do you have? The queries you describe should be very fast unless you're managing millions of records. –  rmarimon Nov 11 '09 at 4:24
    
i only have 12689 (10k++) records. I though mysql can handle millions of records? I notice that, after reach 10k++, it becomes slower, it might because of numbers of rows... I need help, how to make it faster???? –  mysql_learner Nov 11 '09 at 4:30
    
Have you ever optimized or re-indexed the tables? The GUI can do this for you, without needing the commands... 1)ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... 2)REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM] 3)OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name Please note I cannot be held responsible is anything breaks on this. I have never had a problem. I am just stating. dev.mysql.com/doc/refman/5.1/en/table-maintenance-sql.html –  lilott8 Nov 11 '09 at 14:20

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.