What is the difference between single or composite column indices?

Question

In any relational database, we can create indexes that increase query speed. But creating a larger index can damage the update / insert speed, because the Db system will have to update each index when new data arrives (insert, update, merge, etc.).

We use an example. we can create index index1 ADD INDEX index1 ( order_id ASC, buyer_id ASC) OR we can create 2 indexes, index2 and index3 ADD INDEX index2 ( order_id ASC) ADD INDEX index3 ( buyer_id ASC)

In such a query, select * from tablename, where order_id> 100 and buyer_id> 100

Which one is faster? Using Index1 or index2 and index3?

On the other hand, when I insert or update the equation, I assume that it will be much faster to use one index instead of 2, but I have not tested it on the MySql or MSSQL server, so I can’t be like that of course. If anyone has experience in this matter, please share it.

And the last, as far as int values ​​are concerned, I considered it impossible or advisable to create an index only for int columns, because it does not increase the query time, is this true?

Answer 1

For the exact query that you mentioned, I personally will go for index1 (you will have a search operation for both conditions at once). The same index should also do the work, even if you only filter order_id (because order id is the first column of the index, so the same BTREE structure should help, even if you omit the buyer).

At the same time, index1 will not help if you only filter buyer_id (because BTREE will first be structured by the missing order_id according to the index creation instruction). You are likely to finish scanning the index with index1 , while separate indexes will work in this scenario (searching for index3 is what you should expect).

Answer 2

Index performance is related to its selectivity, the fact of using two indexes, or a complex index, which should be evaluated in the context of its application or query, especially critical for performance only because it reduces the number of rows for processing by fields (as possible as possible) and fits into associations).

In your case, since there is usually only one buyer in the order is not a very selective index order_id, buyer_id (it’s nice to use it to join the operations), since it would be the opposite, buyer_id, order_id to facilitate the search for customer orders