Create index 2 for the database. Next, let's make it a little more complex. what if there is an ORDERBY statement? Believe it or not, most databases will benefit from the index when using orderby. The SELECT statement is a little more complex. what if there is an order by statement? 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 do not need other indexes. we just need to change the query statement slightly. Postgres is used here. we will give the database an extra prompt-add the field in the where statement to the order by 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.
If it cannot be avoided, you should check each table to be combined and use the above policy to create an index, and then use the EXPLAIN command to verify whether the expected index is used. If yes, OK. If not, you may need to create a temporary table to combine them and use appropriate indexes.
Note that creating too many indexes will affect the update and insertion speed, because it needs to update each index file as well. For a table that often needs to be updated and inserted, there is no need to create an index for a rarely used where clause. for a small table, the sorting overhead is not very high, there is no need to create another index.
The above is just a few basic things. In fact, there are a lot of knowledge in it. by explaining, we cannot determine whether this method is optimal, each database has its own Optimizer. although it may not be well-developed, they will compare which method is faster during Query. in some cases, it may not be faster to create an index. for example, when an index is placed in a non-contiguous bucket, this will increase the read burden on the disk. Therefore, the actual environment should be used to determine which one is the best.
In the beginning, if the table is not large and there is no need to make an index, my opinion is to make an index only when necessary, and some commands can be used to optimize the table, for example, MySQL can use "optimize table ".
To sum up, you should have some basic concepts about how to create an appropriate index for the database.
What about the word 'distribute? Believe it or not, most databases will benefit from the index when using order. SELECT...
