You finished your brand new application, everything is working like a charm. Users are coming and using your web. Everybody is happy.
Then, suddenly, a big burst of users kills your MySQL server and your site is down. What went wrong? How can you prevent it?
Here are some tips on MySQL Performance which will help you and help your database.
Think BIG
In the early stage of development you should be aware of expected number of users coming to your application. If you expect many users, you should think big from the very beginning, plan for replication, scalability and performance.
But, if you optimize your SQL code, schema and indexing strategy, maybe you will not need big environment. You must always think twice as performance and scalability is not the same.
Always use EXPLAIN
The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a SELECT statement or as a synonym for DESCRIBE.
When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the SELECT, including information about how tables are joined and in which order. EXPLAIN EXTENDED can be used to provide additional information.
Choose the right data type
Databases are typically stored on disk (with the exception of some, like MEMORY databases, which are stored in memory). This means that in order for the database to fetch information for you, it must read that information off the disk and turn it into a results set that you can use. Disk I/O is extremely slow, especially in comparison to other forms of data storage.
When your database grows to be large, the read time begins to take longer and longer. Poorly designed databases deal with this problem by allocating more space on the disk than they need. This means that the database occupies space on the disk that is being used inefficiently.
Picking the right data types can help by ensuring that the data we are storing makes the database as small as possible. We do this by selecting only the data types we need.
Use persistent connections
The reason behind using persistent connections is reducing number of connects which are rather expensive, even though they are much faster with MySQL than with most other databases.
There are some debate on the web on this topic and mysqli extension has disabled persistent connection feature, so I will not write much more on this topic. The only downside of persistent connections is that if you have many concurrent connections, max_connections setting could be reached. This is easily changed in Apache settings, so I don’t think this is the reason why you should not use persistent connections.
Persistent connections are particularly useful if you have db server on another machine. Because of the mentioned downside, use them wisely.
Learn about Query Cache
The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.
The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.
The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
How do you find out my MySQL query cache is working or not?
MySQL provides the stats of same just type following command at mysql> prompt:
mysql> show variables like 'query%';
Do not use indexed column in a function
Index on a column can be great performance gain, but if you use that column in a function, index is never used.
Always try to rewrite the query to not use the function with indexed column.
WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(event_date) <= 7
could be
WHERE event_date >= '2011/03/15' - INTERVAL 7 DAYS
and today’s date is generated from PHP. This way, index on column event_date is used and the query can be stored inside Query Cache.
Learn the Zen of SQL coding
SQL code is the foundation for optimizing database performance. Master SQL coding techniques like rewriting subquery SQL statements to use JOINS, eliminating cursors with JOINS and similar.
By writing great SQL code your database performance will be great.
Use ON DUPLICATE KEY UPDATE
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.
INSERT INTO wordcount (word, count)
VALUES ('a_word',1)
ON DUPLICATE KEY UPDATE count=count+1;
You are saving one trip to the server (SELECT then UPDATE), cleaning you code up removing all if record_exists insert else update.
If you follow some of this tips, database will be greatful to you.
Source:- http://www.codeforest.net/8-great-mysql-performance-tips