Indexing a Database (ii)

Data | database | Index Then, a little bit more complicated, what if there is an ORDER BY clause? Believe it or not, most databases will benefit from the index when they use the order by.

SELECT * FROM MyTable
WHERE category_id=1 and user_id=2
Order by Adddate DESC;

A little confused, huh? It's very simple, like creating an index for a field in a Where clause, and an index to the field in the word for ORDER by:

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

Look at the output of explain, it seems a bit scary ah, the database to do a more we do not ask for the order, this next to know how the performance damage it, it seems that our database of their own operation is a bit too optimistic, then, to the database a little more hint.

In order to skip the sorting step, we don't need another index, just a slight change to the query statement. Here's the postgres, and we'll give the database an extra hint--in the order BY statement, join the field in the where statement. This is just a technical process, not necessarily, because in fact there is no sort operation on the other two fields, but if you join, 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're using our expected index, and it's smart enough to know that you can start reading from behind the index to avoid any sort.

That's a little bit more detailed, but if your database is huge and your daily page requests are millions, I think you'll get a lot of benefit. But what if you want to do more complex queries, such as combining multiple tables and queries, especially where the fields in the where restrictions are coming from more than one table? I usually try to avoid this because the database has to combine everything in each table and then eliminate the inappropriate rows, which can be costly.

If you can't avoid it, you should look at each table you want to combine and use the above strategy to build the index, and then use the explain command to verify that you have used the index you expect. If so, it's OK. No, you might want to create temporary tables to combine them and use the appropriate index.

Note that creating too many indexes will affect the speed of updates and inserts, as it needs to update each index file as well. For a table that needs to be updated and inserted frequently, there is no need to index a single, rarely used where clause, and for smaller tables, the cost of sorting is not very large and there is no need to establish additional indexes.

The above are just some very basic things, in fact, there are a lot of learning, alone explain we can not determine whether the method is the most optimized, each database has its own some optimizer, although may not be perfect, but they will be in the query when the comparison of which way faster, in some cases, Indexing may not be fast, for example, when the index is placed in a discontinuous storage space, which increases the load on the read disk, therefore, which is the optimal, should be tested by the actual use of the environment.

In the beginning, if the table is not large, there is no need to index, my opinion is to be indexed when needed, but also some commands to optimize the table, such as MySQL can be "OPTIMIZE table."

To sum up, you should have some basic concepts on how to set up a proper index for a database.