Mysql – Big table with simple queries, but with a lot of text, is it bad for performance

MySQL

I am making cache table for website. It will have no problem with indexes (one unique index, simple quires), but it will have a lot of utf text. It will be 10-15gb (2 mln rows).
Caching will help a lot for saving cpu usage, without cache every page has many complex queries.

Is it ok two have such a big table? It will be queried only with "SELECT text WHERE id = $id" several times a second.

Best Answer

MySQL itself can handle primary key selects very efficiently. However cached content does not require transactional engine so it would be much faster to store this kind of data in various key-value storages. Memcached is the first one to consider.

Related Solutions

MySQL table design / architecture, table is too big

Well first there is no way that all of this should ever be in one table. You need related tables for information such as Assignee and Inventor. Please read about normalization.

But in reality are you sure that mysql is the way to go for this since you seem mostly to want to search on data that is not always in the same form. Personally, for this type of thing I would look at NoSQL databases for the text parts in conjuction with a relational database for the data that is easy to describe and determine the size of.

MySQL slow queries – Fast using PHPMyAdmin , low CPU and RAM usage

You need to identify which queries are taking some time to execute and optimize accordingly. By default mysql does not log slow queries, so you have to add the following to your mysql config file -

log-slow-queries
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10     // in seconds - you can replace this with another number.

For any mysql performance issues, it is a good idea to use mysqltuner

Best Answer

Related Solutions

MySQL table design / architecture, table is too big

MySQL slow queries – Fast using PHPMyAdmin , low CPU and RAM usage

Related Question