CODEWITHSUNDEEP
mysqlsqlperformanceindexingsql-order-by
Somnath Muluk
Somnath Muluk

Reputation: 57656

Order by column should have index or not?

I am adding indexes to table with millions of records which is used for searching results. I am showing results in ASC or DESC order. My question is should that column have an index or not? I have 2 more indexes on that table. How will performance be affected by adding or not adding an index to that column?

Upvotes: 28

Views: 19177

Answers (4)

sudheer
sudheer

Reputation: 75

It is depends on your query. MySQL can use a composite index for both where clause and order by . It is If you include an ORDER BY clause explicitly that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query includes GROUP BY but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying ORDER BY NULL.

More details

http://download.nust.na/pub6/mysql/doc/refman/5.1/en/order-by-optimization.html

If you are not good at MySQL optimization better to use some good tools like SmartMySQL which can identify best indexes for your queries based on table structure and it's data. It is free GUI tool which can optimize query and speed up your development 10 times faster compare to MySQL's Workbench.

You can download it from www.smartmysql.com

Upvotes: 2

Lawakush Kurmi
Lawakush Kurmi

Reputation: 2996

Using ORDER BY on indexed column is not a good idea. Actually the purpose of using index is to making searching faster so the index column helps to maintain the data in sorted order. I will suggest you to use ORDER BY at non-indexed column. I had run a sample query on the MySql and get the result as mentioned below.

SELECT 
    ci.id AS item_id, ci.brand_id, ci.category_id, 
    ci.item_size_type, ci.is_express_processing, ci.packing, 
    ci.parent_service_id, ci.product_id, ci.size,
    ci.create_date AS item_create_date, cis.service_id, cis.quantity   
FROM 
    cart AS c 
INNER JOIN 
    cart_items AS ci ON ci.cart_id = c.id 
LEFT JOIN
    cart_item_services AS cis ON cis.item_id = ci.id 
WHERE
    c.id = 144 
ORDER BY 
    c.create_date;

Here I am using ORDER BY on a non-indexed column create_date and result is as follows:

# Time: 2017-11-03T10:30:33.237056Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 0.000340  
# Lock_time: 0.000154 Rows_sent: 2  Rows_examined: 4

And now I am going to use ORDER BY on an indexed column item_id:

SELECT 
    ci.id AS item_id, ci.brand_id, ci.category_id, 
    ci.item_size_type, ci.is_express_processing, ci.packing, 
    ci.parent_service_id, ci.product_id, ci.size,
    ci.create_date AS item_create_date, cis.service_id, cis.quantity   
FROM 
    cart AS c 
INNER JOIN 
    cart_items AS ci ON ci.cart_id = c.id 
LEFT JOIN
    cart_item_services AS cis ON cis.item_id = ci.id 
WHERE
    c.id = 144 
ORDER BY 
    item_id;

Now I got the result as follows.

# Time: 2017-11-03T10:30:47.802392Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 0.000383  
# Lock_time: 0.000176 Rows_sent: 2  Rows_examined: 4

So in the above result we can see the indexed columns Query_time and Lock_time is higher than the non-indexed column.

For more detailed analysis about query standard and performance you can refer below link:

http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21766

Actually - for some millions rows better to apply some well thought out index, it is not so big dataset for beginning to worry about space-performance issues.

but

If you read that table once a day and update/delete rows 100 times per second - then effect from the index may degrade performance of main operations, while occasinally selecting will perform better.

So, the answer as usual - it depends

Upvotes: 6

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

Order by columns are used for ordering the result set, not filtering. An index on the columns mentioned in the order by clause is unlikely to change anything, especially if it's not used to filter the data.

Upvotes: 27

Related Questions