Indexing the database (2)_PHP tutorial

Next, 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 * 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 cut short to "Mytable_categoryid_Adddda"
Create
Explain Select * from mytable
WHERE C ategory_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 sort that we did not require. Now you know how the performance is damaged. It seems that we are a bit too optimistic about the operation of the database itself, so let's give the database a little more hints.
In order to skip the sorting step, we will give the database an additional Tips--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.

http://www.bkjia.com/PHPjc/631059.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631059.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 *...