Indexing the database (1)_PHP tutorial

Like many PHP developers, when I first started building dynamic websites, I used relatively simple data structures. PHP is indeed very convenient in connecting to databases (Translator's Note: Some people think that PHP does not have a unified interface when connecting to different databases, which is inconvenient. In fact, this can be done through some extension libraries), you don't need to You can build and use a database by looking at a large number of design documents, which is one of the main reasons for PHP's success.
Some time ago, a rather senior programmer asked me what an index was. I was very surprised. I thought this would never be a drop in the ocean, because there are thousands of developers (probably most of them). (who use MySQL) have no formal training on databases. Although they have done some development for customers, they know less about how to build appropriate indexes for databases, so I came up with the idea of ​​writing a related article. .
The most common case is to build an index for the fields that appear in the where clause. To facilitate the description, we first create a table as follows.
CREATE TABLE mytable (
id serial primary key,
category_id int not null default 0,
user_id int not null default 0,
);
It’s very simple, but it’s enough to explain this problem. If you often use statements similar to the following when querying:
SELECT * FROM mytable WHERE category_id=1;
The most direct way to deal with it is to create a simple index for category_id:
CREATE INDEX mytable_categoryid
ON mytable (category_id);
OK, done? Don't be happy yet, what if you have more than one selection criteria? 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, this is not an optimal approach. You can create multiple indexes.
CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);
Notice my naming habits? I use the method of "table name_field 1 name_field 2 name". You'll soon find out why I do this.
Now that you have indexed the appropriate fields, you are still a little worried. You may ask, will the database actually use these indexes? Just test it. For most databases, this is very 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 uses an index when querying (a good start), and it uses the second index I created. See the benefit of my naming above, you immediately know it uses the appropriate index.

http://www.bkjia.com/PHPjc/631058.html

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631058.htmlTechArticleLike many PHP developers, when I first started building dynamic websites, I used relatively simple data structure. PHP is indeed very convenient in connecting to databases (Translator...