Indexing the database (i)

Data | database | Index just like many PHP developers, I used relatively simple data structures when I first started building dynamic websites. PHP is really very convenient to connect to the database (translator Note: Some people think that PHP is connected to different databases without a unified interface, inconvenient, in fact, this can be done through a number of extension libraries), you do not have to look at a large number of design documents can build and use the database, This is one of the main reasons for the success of PHP.

Some time ago, I was amazed that a fairly advanced programmer asked me what an index was, and I don't think it would be bucket, because thousands of developers (probably most of them using MySQL) were not properly trained in the database, even though they had done some development for their clients, But there is less knowledge about how to set up an appropriate index for the database, so I have the idea of writing a related article.

The most common scenario is to create an index for the field that appears in the WHERE clause. To make it easier to tell, let's start with a table like the following.

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 very simple, but it's enough to illustrate the problem. If you use a statement similar to the following in your query:

SELECT * FROM MyTable WHERE category_id=1;

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

CREATE INDEX Mytable_categoryid
On MyTable (category_id);

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

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

Your first reaction may be to create an index for user_id. No, that's not the best way. You can build multiple indexes.

CREATE INDEX Mytable_categoryid_userid on MyTable (category_id,user_id);

Have you noticed my habit of naming? I use the "table name _ Field 1 _ Field 2 Names" method. You'll soon find out why I did it.

Now that you've indexed the right fields, it's still a bit of a worry, you might ask, does the database actually use these indexes? The test 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

This is the Postgres data, and you can see that the database uses an index (a good start) at the time of the query, and it uses the second index I created. See the benefits of naming me above, you immediately know it uses the appropriate index.