Just like many PHP developers, I used a relatively simple data structure when I started building dynamic sites. PHP in connection with the database is really very convenient (translator note: Some people think that PHP is not a unified interface to connect different databases, it is not very convenient, in fact, this can be done through a number of extension libraries to do this), you do not have to look at a large number of design documents to build and use the database, This is one of the main reasons for PHP's success.
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.
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.
http://www.bkjia.com/PHPjc/531988.html www.bkjia.com true http://www.bkjia.com/PHPjc/531988.html techarticle Just like many PHP developers, I used a relatively simple data structure when I started building dynamic sites. PHP in connection with the database is really very convenient (translator ...
