Create an index for the database (2)

Next, let's make it a little more complex. What if there is an order by clause? Believe it or not, most databases will benefit from the index when using order.
SELECT * FROM mytable
WHERE category_id = 1 AND user_id = 2
Order by adddate DESC;
A little confused, right? Just like creating an index for a field in the where clause, it also creates an index for the field 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
"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
Let's look at the EXPLAIN output. It seems a little scary. The database has done more sorting that we don't need. Now we know how the performance is damaged, it seems that we are a little optimistic about the operation of the database itself, so let's give the database a little more tips.
In order to skip the sorting step, we will continue sorting? Why? Is it true ?? Gray ?? Why? Zookeeper inner UEI cadmium Mei Ne? Ostgres, we will give the database an extra prompt-in the order by statement, add the field in the where statement. This is only a technical process, and it is not necessary, because in fact, there is no sorting operation on the other two fields, but if you add, 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 we use the expected index, and it is quite intelligent. We know that we can start to read the index, thus avoiding any sorting.
The above is a little more detailed. However, if your database is huge and your daily page requests reach millions, I think you will benefit a lot. However, if you want to perform more complex queries, such as combining multiple tables for query, especially when the where restriction clause contains fields from more than one table, what should you do? I usually try to avoid this practice, because the database should combine all the items in each table, and then exclude the unsuitable rows, which may cause great overhead.