Experience with MySQL (ix.) Federated Index

Note: Index (name,age) represents the establishment of a federated index on the name,age two column

Since indexes have a critical impact on the query performance of the database, here are some of my summaries and experiences:

A query can only use one index at a time: select name from user where name= ' Plantegg ' and age>35 if Index (name); Index (age), the MySQL query optimizer will automatically select a cable to use;

Which index MySQL chooses, can look like this: mysql> show index from photo;

+-------+------------+------------------------+--------------+---------------+-----------+----------- --+------- ---+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-------+------------+------------------------+----- ---------+---------------+-----------+-------------+------- ---+--------+------+------------+--------- +

|           Photo | 0 |             PRIMARY | 1 | photo_id |       A |     237871 | NULL |       NULL | |         Btree | |

|           Photo | 1 |             Index_random | 1 | Random |       A |     237871 | NULL | NULL | YES |         Btree | |

|           Photo | 1 |             fk_photo_profile_id | 1 | profile_id |       A |     237871 | NULL |       NULL | |         Btree | |

|           Photo | 1 |             fk_photo_temp_photo_id | 1 | temp_photo_id |       A |     237871 | NULL | NULL | YES |         Btree | |

|           Photo | 1 |             fk_photo_album_id | 1 | album_id |       A |     237871 | NULL | NULL | YES |         Btree | |

+-------+------------ +------------------------+--------------+---------------+-----------+-------------+------- ---+--------+- -----+------------+---------+

The larger the cardinality indicates the finer the index candidate points (the default is the Btree index);

You can also try Force Index to force an index to see if the speed is MySQL is not query faster (if that's the case you need analyze yourtable, MySQL recalculates your cardinality to help him choose index correctly

Careful analysis of the results of explain: focus on Extra,key, Rows,select_type results!

In a careful query, the group BY, order by, and so on, basically such queries will appear in the explain: Using where; Using temporary; Using Filesort

A federated index is used with care, when index (name,age), if where name= ' PP ' can use the index, where age=25 can not use indexes, where name= ' pp ' and age>25 to use the index; where Nam e = ' pp ' order by age can use the index, where name> ' PP ' is not allowed to use the index, but where name> ' pp ' is name,a   GE can use index, please pay close attention to the difference; The order BY name ASC Age DESC will not be able to use the index!

Indexing can only help your query when it is added to memory, and if the index is too big to put in memory, the index is meaningless! You also need to Random aceess disk to access the index slower than no index!

Select can be used without a select *, that is, what columns are required to take only those columns (hibernate those that are not good for performance), such as: At index (Name), select * from user where Name like ' pp% ' and select name from user where name like ' pp% ' vary in performance, and if there are 10,000 records-compliant results (1 billion records for the user table) the previous query may take 2 minutes (assuming your system is IO per second) PS look) after a query may only take 0.01 seconds! Since the previous query is going to take out all 10,000 of these records from the hard disk, the last query takes name from the index in memory. The latter query you explain will see Using Index in extra.

Always be wary of random access to the disk, sequential read-write and random access performance difference is N-level (in order to read and write your OS, dis h Cache this time to show off) to this question if you are interested in the use of C to write a test program, Random Read and write when constantly fseek, phase should be the same function you do not fseek, but through the sequential read and write to memory, in memory to throw away those should be disk to fseek place, you should understand what I mean!

After 5.0.27, MySQL supports set profling=1, which allows you to analyze your SQL statements in detail every step of the time consuming

You'll be pleasantly surprised if there's a limit + index fit in the order by.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/