Menu
I making indexes to table with millions of records which is used for searching result. I am showing results by ASC or DESC order. My question is that column should have index or not? I am 2 more indexes on that table. How performance will affect by making or not making index to that column?
336
Yes, index will help you, when using ORDER BY. Because INDEX is a sorted data structure, so the request will be executed faster.
In general, you should create an index on a column in any of the following situations: The column is queried frequently. A referential integrity constraint exists on the column. A UNIQUE key integrity constraint exists on the column.
No, you should not index all of your columns, and there's several reasons for this: There is a cost to maintain each index during an insert, update or delete statement, that will cause each of those transactions to take longer. It will increase the storage required since each index takes up space on disk.
Using the indexes can improve the performance of the sorting operation because the indexes create an ordered structure of the table rows so that the storage engine can fetch the table rows in a pre-ordered manner using the index structure.
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.
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
