Do I need to add an index to the ORDER BY field?

Question

I have a request of this type

$query = "SELECT * FROM tbl_comments WHERE id=222 ORDER BY comment_time"; 

Do I need to add an index to the comment_time field?

Also, if I want to get data between two dates, then how do I build an index?

Answer 1

Yes, the index will help you when using ORDER BY. Because INDEX is a sorted data structure, the query will be faster.

Take a look at this example: table test2 with 3 rows. I used LIMIT after ordering to show the difference in execution.

 DROP TABLE IF EXISTS `test2`; CREATE TABLE `test2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `value` varchar(10) CHARACTER SET utf8 COLLATE utf8_swedish_ci NOT NULL, PRIMARY KEY (`id`), KEY `ix_value` (`value`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of test2 -- ---------------------------- INSERT INTO `test2` VALUES ('1', '10'); INSERT INTO `test2` VALUES ('2', '11'); INSERT INTO `test2` VALUES ('2', '9'); -- ---------------------------- -- Without INDEX -- ---------------------------- mysql> EXPLAIN SELECT * FROM test2 ORDER BY value LIMIT 1\G *************************** 1. row ************************* id: 1 select_type: SIMPLE table: test2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using filesort 1 row in set (0.00 sec) 

MySQL checked 3 lines to output the result. After CREATE INDEX we get the following:

 mysql> CREATE INDEX ix_value ON test2 (value) USING BTREE; Query OK, 0 rows affected (0.14 sec) -- ---------------------------- -- With INDEX -- ---------------------------- mysql> EXPLAIN SELECT * FROM test2 ORDER BY value LIMIT 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test2 type: index possible_keys: NULL key: ix_value key_len: 32 ref: NULL rows: 1 Extra: Using index 1 row in set (0.00 sec) 

Now MySQL uses only 1 row.

Responding to the comments I received, I tried the same query without LIMIT:

 -- ---------------------------- -- Without INDEX -- ---------------------------- mysql> EXPLAIN SELECT * FROM test2 ORDER BY value\G *************************** 1. row ****************** id: 1 select_type: SIMPLE table: test2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using filesort -- ---------------------------- -- With INDEX -- ---------------------------- mysql> EXPLAIN SELECT * FROM test2 ORDER BY value\G *************************** 1. row ***************** id: 1 select_type: SIMPLE table: test2 type: index possible_keys: NULL key: ix_value key_len: 32 ref: NULL rows: 3 Extra: Using index 

As we can see, it uses the index for the 2nd ORDER BY .

To create an index in your field, use this:

 CREATE INDEX ix_comment_time ON tbl_comments (comment_time) USING BTREE; 

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Answer 2

Technically, you don’t need indexes on each field, as this will work too, however for performance reasons you may need one or more.

EDIT

This problem has been known since the beginning of software development. Usually, if you increase the amount of memory used by a program, you decrease its speed (provided that the program is well written). Assigning an index to a field increases the data used by db, but speeds up the search. If you do not want to search for anything in this field (you really do in the question), this would be optional.

In the modern era, indexes are not so large compared to the size of data on disk, and adding one or more should not be a bad idea.

It is usually very difficult to say "do I need an index or not." Some help is provided by the EXPLAIN operator ( see the manual ).

Answer 3

As for your first question, you do not need to create an index in comment_time. If the number of records is very large, you will need indexes to speed up the search. But for your operation you do not need indexes. For your second question, using a WHERE clause like this will help you.

 WHERE(comment_time BETWEEN 'startDate' AND 'endDate'); 

Answer 4

The index in the comment_time field may not help at all for such a request:

 SELECT * FROM tbl_comments WHERE id=222 ORDER BY comment_time; 

The query must scan the table to find the corresponding id values. This can be done by looking at the index, looking at the rows, and doing the test. If there is one row that matches, and it has highext comment_time , then this requires scanning the index and reading the table.

Without an index, it will scan the table, find a row and very quickly sort 1 row. A sequential table scan will usually be faster than an index scan followed by a page search (and will definitely be faster in a table that exceeds available memory).

On the other hand, an index on id, comment_time would be very useful.

Answer 5

You do not need to specify an index in comment_time if your identifier is different.

Answer 6

To increase the speed of data retrieval, you will need an index. This will also work with the index. For your second question, you can use the WHERE and BETWEEN clauses.

Contact: http://www.w3schools.com/sql/sql_between.asp

Answer 7

The EXPLAIN statement is very useful in such situations. Upon your request, you will use it as follows:

 EXPLAIN SELECT * FROM tbl_comments WHERE id=222 ORDER BY comment_time 

This will lead to the conclusion which indexes are used to execute the query, and allows you to experiment with different indexes to find the best configuration. To speed up the sorting, you will need the BTREE index, since it stores data in a sorted way. To speed up the search for elements with a specific identifier, the HASH index is the best option because it provides a quick search for equality predicates. Please note that MySQL will not be able to use a combination of both indexes to execute your query and will only use one of them instead.

Additional information: http://dev.mysql.com/doc/refman/5.7/en/using-explain.html

For range predicates, such as dates in a date range, the BTREE index will work better than the HASH index.

Additional information: http://dev.mysql.com/doc/refman/5.7/en/create-index.html