Optimizing MySQL speed on a table with many rows: what is the best method to handle it?
I am developing a chat application. I want everything to be written into a table (ie "Who said what and when"). Hope I have some lines in the near future. I was wondering: what is the best way to optimize the table, knowing that I will often insert rows, and sometimes group reading (i.e. show the whole conversation from the user (look when he / she logged in / started chatting and then looked when he / she leaves, then show the whole conversation)).
This table should be able to handle (hopefully though!) Many rows. (15,000 / day => 4.5 M every month => 54 M rows at the end of the year).
Conversations older than 15 days can be interpreted (but I don't know how I should get it right).
Any idea?
a source to share
54 million lines is not that much, especially during the year.
If you are going to generate a lot of data periodically, I would recommend using the MyISAM and MERGE tables. Since you will not be deleting or editing records, you will not have locking problems if the concurrency parameter is set to 1. Inserts are always added at the end of the table, so SELECT and INSERT can run at the same time, So you do not need to use InnoDB based tables ( which MERGE tables can use).
You can have 1 table per month named the same as data200905, data200904, etc. Your merge table will include all the base tables you need to look for. Inserts are done in the merge table, so you don't have to worry about changing names. When it's time to rotate the data and create a new table, just update the MERGE table.
You can even create multiple MERGE tables based on quarter, years, etc. One table can be used in multiple MERGE tables.
I made this tweak on databases that added 30 million records per month.
a source to share
I have two tips for you:
- If you expect a lot of low priority entries. Then you get better off with indexes as much as possible. Indexes will make insertion slower. Add only what you really need.
- If the logs table is getting bigger and more overtime you should consider a log rotation. Otherwise, you may end up with one giant damaged table.
a source to share
Mysql handles very large datasets surprisingly well with little more than the default setting and database indexes. I started a site that had millions of rows in the database and was able to start it in mysql mode.
Mysql has an "archive" parameter for machine translation to handle many strings, but the lack of index support will make it not very good for you, except perhaps for historical data.
Index creation is required, but you will have to balance them, not just create them because you can. They will allow for faster queries (and would be required to use queries on a large table), but the more indexes you have, the more overhead the insert will be.
If you are just querying your user id column the index won't be a problem there, but if you want to do full text queries in posts, you may only need to index the user column in mysql and using something like sphynx or lucene for full text search since full text search in mysql is not the fastest and slows down insert times significantly.
a source to share
You can handle this with two tables: one for the current chat history and one archive table. At the end of the period (week, month or day depending on your traffic), you can archive current chat messages, delete them from the small table and add them to the archive.
This way, your application will handle the most common case of requesting the current chat status, and it will be very fast.
For queries like "what did x say last month" you will be querying the archive table and it will take a little longer, but that's ok as there won't be any queries like that and if someone is doing a search they'd like to wait some more a couple of seconds.
Depending on your use cases, you can expand on this principle - if there are many requests for chat messages in the last 6 months, save them in a separate table.
A similar principle (for a completely different area) is used by the .NET garbage collector, which has different storage for short lived objects, long lived objects, large objects, etc.
a source to share