Create an index for the database (1 ). Like many PHP developers, I used a relatively simple data structure when I first set up a dynamic website. PHP is indeed very convenient in connecting to the database (just like many PHP developers, I used a relatively simple data structure when I first set up a dynamic website. PHP is indeed very convenient in connecting to the database (Note: Some people think that PHP does not have a unified interface when connecting to different databases, which is not very convenient, in fact, this can be done through some extension libraries. you can create and use databases without looking at a large number of design documents. this is one of the main reasons for PHP's success.
Some time ago, a very senior programmer actually asked me what index is, which surprised me very much. I think it will never be a hit in the ocean, because thousands of developers (probably most of them using MySQL) have not received formal training on databases, even though they have done some development for customers, however, I do not know much about how to create appropriate indexes for the database, so I started to write a related article.
The most common case is to create an index for the field that appears in the where clause. For the sake of convenience, we should first create the following table.
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 is enough to explain this problem. If you use statements similar to the following in queries:
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, OK? Don't be happy. what if you have more than one choice? For example:
SELECT * FROM mytable WHERE category_id = 1 AND user_id = 2;
Your first possible response is to create an index for user_id. No. this is not the best method. You can create multiple indexes.
Create index mytable_categoryid_userid ON mytable (category_id, user_id );
Have you noticed my habits in naming? I use "table name_field 1 name_field 2 name. You will soon know why I did this.
Now you have created an index for an appropriate field. However, it is a bit difficult. you may ask, will the database actually use these indexes? Test it. for most databases, this is very easy. you only need to use the EXPLAIN command:
EXPLAIN
SELECT * FROM mytable
WHERE category_id = 1 AND user_id = 2;
This is what calls s 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 IPVs data. we can see that the database uses an index (a good start) during query, and it uses the second index I created. See the benefits of my naming above. you will immediately know that it uses the appropriate index.
Bytes. PHP is indeed very convenient in connecting to the database...
