Indexing the database 2_PHP tutorial

Next, let’s do something a little more complicated. What if there is an ORDER BY clause? Believe it or not, most databases will benefit from indexing when using order by.
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;
A little confused? Very simple, just like creating an index for the fields in the where clause, also create an index for the fields 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
Looking at the output of EXPLAIN, it seems a bit scary. The database has done an extra sorting that we did not require. Now you know how the performance is damaged. It seems that we are not familiar with the database itself. The operation is a bit too optimistic, so give the database a little more hints.
In order to skip the sorting step, we don’t need any other indexes, we just need to change the query statement slightly. Postgres is used here, and we will give the database an extra hint-in the ORDER BY statement, add the fields in the where statement. This is just a technical process and is not necessary, because there will not actually be any sorting operations on the other two fields, but if added, 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 uses the index we expected, and it is quite smart and knows that it can start reading from behind the index. This avoids any sorting.
The above is a bit detailed, but if your database is very large and your daily page requests reach millions, I think you will benefit a lot. However, what if you want to do a more complex query, such as combining multiple tables to query, especially when the fields in the where restriction clause come from more than one table, how should you deal with it? I usually try to avoid this approach, because the database has to combine everything in each table and then exclude those inappropriate rows, which may be very expensive.
If it cannot be avoided, you should check each table to be combined and use the above strategy to create an index, and then use the EXPLAIN command to verify whether the index you expected is used. If so, OK. If not, you may want to create temporary tables to join them together, and use appropriate indexes.
It should be noted that creating too many indexes will affect the speed of updates and inserts, because it requires updating each index file equally. For a table that frequently needs to be updated and inserted, there is no need to create a separate index for a rarely used where clause. For smaller tables, the sorting overhead will not be very large, and there is no need to create additional indexes.
The above introduction is only some very basic things. In fact, there is a lot of knowledge in it. We cannot judge whether the method is the most optimized by EXPLAIN alone. Each database has its own optimizer, although it may still be It is not perfect, but they will all compare which method is faster when querying. In some cases, establishing an index may not be faster. For example, when the index is placed in a discontinuous storage space, this will increase the time required to read the disk. burden, therefore, which one is optimal should be tested through the actual usage environment.
At the beginning, if the table is not large, there is no need to index. My opinion is to index only when needed. You can also use some commands to optimize the table, such as "OPTIMIZE TABLE" for MySQL.
In summary, you should have some basic concepts on how to create appropriate indexes for your database.

http://www.bkjia.com/PHPjc/531986.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/531986.htmlTechArticleNext, let’s get a little more complicated. What if there is an ORDER BY clause? Believe it or not, most databases will benefit from indexing when using order by. SELECT...