How to optimize MySQL database

1. Add primary Key ID

2. Try to avoid using SELECT * Form table

3. Create an index
Indexing is especially important for queries that are the primary application. A lot of times the performance problem is simply because we forgot to add an index, or we didn't add a more efficient index. If you don't index it, look for anything even if it's just

A full table scan is performed for a particular piece of data, and if the amount of data in a table is large and the results of the match are rare, then non-indexing can lead to a fatal performance degradation. But it's not a situation that has to be indexed, for example, sex is probably just two.

Value, indexing not only has no advantage, it also affects the update speed, which is called over-indexing.
4. Composite Index
For example, there is a statement like this: SELECT * from users where area= ' Beijing ' and age=22; if we were to create a single index on area and age, because MySQL query can only use one index at a time, it is

Full table scanning improves efficiency by relatively non-indexing, but it is more efficient to create composite indexes on the area and age two columns. If we create a composite index (area, age, salary), it is actually equivalent to creating a

(Area,age,salary), (Area,age), (area) Three indexes, which is called the best left-prefix attribute. Therefore, when creating a composite index, the columns that are most commonly used as constraints should be placed on the leftmost, decreasing in turn.
4, the index does not contain columns with null values
This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.
5. Use short index
Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. A short index can not only improve the query

Speed and can save disk space and I/O operations.
6. Sorted Index Issues
The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations when the database default sort can meet the requirements; Try not to include multiple

Sort the columns, and if necessary, create a composite index for those columns.
7. Like statement operation
It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.
8. Do not perform calculations on columns
SELECT * from the users where year (adddate) <2007; will operate on each row, which will cause the index to fail and perform a full table scan, so we can change to select * from users where adddate< ' 2007-01-01 ';
9. Do not use not in and <> operation
None in and <> do not use the index for full table scanning. Not in can be replaced by not exists, id<>3 can be replaced with id>3 or id<3.

10. optimize MySQL Query cache

     query on the MySQL server to enable high-speed query caching. Having the database engine quietly handled in the background is one of the most effective ways to improve performance. When the same query is executed multiple times, it is fairly fast if the result is extracted from the cache.
But the main problem is that it is so easily hidden that most of our programmers will ignore it. In some processing tasks, we can actually prevent the query cache from working.
1.//query cache does not work
2. $r = mysql_query ("Select username from user WH ERE signup_date >= curdate () ");
3.
4.//Query Cache works!
5. $today = Date ("y-m-d");
6. $r = mysql_query ("Select username from user WHERE signup_date >= ' $today '");
7.
8.//query cache does not work
9. $r = mysql_query ("Select username from user WHERE signup_date >= curdat E () ");
Ten.
.//Query Cache works!
$today = Date ("y-m-d");
$r = mysql_query ("Select username from user WHERE signup_date >= ' $today '");

11. Use limit 1 to get unique rows
Sometimes, when you're querying a table, you know you just need to look at one line. A very unique record that you might go to, or just check the number of records that exist, they all satisfy your WHERE clause.
In this case, adding a limit of 1 will make your query more efficient. This allows the database engine to discover that only 1 will stop scanning, rather than scanning the entire table or index.
1.//Do I have any users from Alabama?
2.//What does not have:
3. $r = mysql_query ("SELECT * from user WHERE state = ' Alabama '");
4. If (mysql_num_rows ($r) > 0) {
5.//...
6.}
7.///Much better:
8. $r = mysql_query ("Select 1 from user WHERE state = ' Alabama ' LIMIT 1");
9. If (mysql_num_rows ($r) > 0) {
10.//...
One.}

12. Do not use the by RAND () command
This is a trap that many novice programmers will fall into. You may have made a terrible peace unconsciously. This trap is created when you use the by RAND () command.
If you really need to show your results randomly, there are many better ways to achieve them. Admittedly, this will require more code, but it avoids the performance bottleneck. The problem is that MySQL may execute the by RAND () command for each individual row in the table (which consumes the processor's processing power), and then gives you just one row back.
1.//What does not:
2. $r = mysql_query ("Select username from the user ORDER by RAND () LIMIT 1");
3.//Much better:
4. $r = mysql_query ("SELECT count (*) from user");
5. $d = Mysql_fetch_row ($r);
6. $rand = Mt_rand (0, $d [0]-1);
7.
8. $r = mysql_query ("Select username from user LIMIT $rand, 1");

How to optimize MySQL database