Indexing the MySQL database

Some time ago, a rather senior programmer asked me what is called the index, I am very surprised, I think this will not be bucket, because there are thousands of developers (probably mostly using MySQL) have not received the formal training of the database, although they have done some development for the customer, But I know less about how to build the right index for the database, so I thought about writing an article about it.

The most common scenario is to build an index for the field that appears in the WHERE clause. To facilitate the narration, we first set up a table as follows.

The code codes are as follows:

CREATE TABLE MyTable ( ID Serial primary Key, category_id int NOT NULL default 0, USER_ID int NOT NULL default 0, adddate int NOT NULL default 0 );

It's easy, but it's enough to illustrate the problem. If you use a statement similar to the following at query time:

SELECT * FROM MyTable WHERE category_id=1;

The most straightforward response is to create a simple index for category_id:

CREATE INDEX Mytable_categoryid
On MyTable (category_id);

OK, get it done? Don't be happy, if you have more than one choice. For example:

SELECT * FROM MyTable WHERE category_id=1 and user_id=2;

Your first reaction might be to create an index for user_id. Not good, this is not the best way. You can build multiple indexes.

CREATE INDEX Mytable_categoryid_userid on MyTable (category_id,user_id);

Did you notice my habit of naming it? I use "Table name _ Field 1 _ Field 2 Name" way. You'll soon know why I did it.

Now that you've indexed the appropriate fields, it's a bit of a worry, you might ask, does the database actually use these indexes? Testing is OK, for most databases, this is easy, just use the explain command:

EXPLAIN

SELECT * FROM MyTable
WHERE category_id=1 and user_id=2;

This is what Postgres 7.1 returns (exactly as I expected)

Notice:query PLAN:

Index Scan using Mytable_categoryid_userid on
MyTable (cost=0.00..2.02 Rows=1 width=16)

EXPLAIN

The above is the Postgres data, you can see that the database used an index when querying (a good start), and it is using the second index I created. See the benefits of naming me above, you immediately know it uses the appropriate index.

And then, a little bit more complicated, what if there's an order by sentence? Believe it or not, most databases will benefit from the index when they use the order by.

SELECT * FROM MyTable
WHERE category_id=1 and user_id=2
ORDER by Adddate DESC;

A little confused, huh? As simple as creating an index for a field in the WHERE clause, an index is also created for the field in the ORDER BY clause:

CREATE INDEX Mytable_categoryid_userid_adddate
On MyTable (category_id,user_id,adddate);

Note: "Mytable_categoryid_userid_adddate" will be truncated to

"Mytable_categoryid_userid_addda"

CREATE

EXPLAIN SELECT * FROM MyTable
WHERE category_id=1 and user_id=2
ORDER by Adddate DESC;

Notice:query PLAN:

Sort (cost=2.03..2.03 Rows=1 width=16)
-Index Scan using Mytable_categoryid_userid_addda
On MyTable (cost=0.00..2.02 Rows=1 width=16)

EXPLAIN

Look at the output of explain, it seems a bit scary ah, the database did a we do not ask for a sort, this is how the performance is damaged, it seems that we are a bit too optimistic about the database itself, then, to the database a little more hints.

To skip the sorting step, we don't need another index, just change the query statement a little bit. Here's the postgres, we'll give the database an extra hint--in the order BY statement, add the field in the where statement. This is only a technical process and is not necessary because there is actually no sort operation on the other two fields, but if you join, Postgres will know what it should do.

EXPLAIN SELECT * FROM MyTable
WHERE category_id=1 and user_id=2
ORDER by category_id desc,user_id desc,adddate DESC;

Notice:query PLAN:

Index Scan Backward using
Mytable_categoryid_userid_addda on MyTable
(cost=0.00..2.02 Rows=1 width=16)

EXPLAIN

Now we're using the index we expect, and it's pretty smart, knowing that you can start reading from behind the index, avoiding any sort of ordering.

That's a little bit more, but if your database is huge and the daily page requests are millions, I think you'll benefit a lot. But what if you want to make more complex queries, such as querying multiple tables together, especially if the fields in the where constraint are from more than one table? I usually try to avoid this, because the database has to combine everything in each table and then exclude the inappropriate rows, which can be costly.

If you can't avoid it, you should look at each table you want to combine, and use the above strategy to build the index, and then use the explain command to verify that you have used the index you expect. If it is, then OK. If not, you may want to create a temporary table to combine them and use the appropriate index.

It is important to note that too many indexes will affect the speed of updates and insertions because it requires the same update for each index file. For a table that often needs to be updated and inserted, there is no need to index a rarely used where clause, and for smaller tables, the cost of sorting is not significant and there is no need to create additional indexes.

The above is only a few very basic things, in fact, there are a lot of learning, explain alone we can not determine whether the method is the most optimized, each database has its own some of the optimizer, although may not be perfect, but they will be compared in the query of which way faster, in some cases, Indexing is not necessarily fast, for example, when the index is placed in a discontinuous storage space, which increases the load on the read disk, and therefore, which is optimal, should be verified by the actual use of the environment.

In the beginning, if the table is not large, there is no need to index, my opinion is to be indexed when needed, there are also some commands to optimize the table, such as MySQL available "OPTIMIZE table."

In summary, you should have some basic concepts on how to build the right index for your database.

Indexing the MySQL database