Does creating index on a column which is there in sort order improves performance? I need to know this for Mysql, Postgresql and Oracle database.
E.g query:
select * from article_comments where article_id = 245 order by date_created desc limit 30;
In article_comments table, article_id is an indexed field, where as date_created is not. If we create an index on date_created, will it improve performance. Table size is around 5-7 million rows.
Does creating index on a column which is there in sort order improves performance?
A general answer is: it depends on many factors, esspecialy on conditions used in WHERE clause.
In case of your query an index on date_created column doesn't eliminate a sort operation due to where article_id = 245 clause.
In this case you need to create a multicolum index in order to skip sorting:
CREATE INDEX somename ON article_comments(article_id, date_created DESC)
WHERE clause was handled by that index; the ORDER BY could still be handled by the rest of the index columns. Hence, the order of the index rows allows the sort pass to be skipped.I can tell only for Oracle. Yes an Index can make your sorting faster.
In this example Function-Based Index for Language-Dependent Sorting Oracle shows an index which intention is only to improve performance of an ORDER BY operation.
Another example regarding sorting is shown here: Full Index Scan
ALTER INDEX index INVISIBLE / VISIBLE;which enables you to test without creating and dropping the index again and again.