5

In any relational Databases, we can create indexes that boost query speed. But creating more index can damage update/insert speed because the Db system will have to update each index when new data coming (insert, update, merge etc)

We use an example. we can create a index called 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 a query like this select * from tablename where order_id>100 and buyer_id>100

Which one is faster? By using Index1 or index2 and index3?

On the other side of the equation, when inserting or updating, I assume it will be much faster to just use one index instead of 2 but I haven't tested it against MySql or MSSQL server so I can't be so sure. If anyone has experience on that matter, do share it please.

And the last thing is about int typed values, I thought it's not possible or relevant to create a index just for int type columns because it doesn't boost the query time, is it true?

Muhammad Muazzam
  • 2,810
  • 6
  • 33
  • 62
shawhu
  • 1,217
  • 1
  • 12
  • 27
  • 2
    Also note that indexing in Sql Server and MySql are different, so tagging this question with both is problematic as both can have different answers. – Vanlightly Nov 20 '15 at 07:19
  • I was intended to link this one http://stackoverflow.com/questions/179085/multiple-indexes-vs-multi-column-indexes – Pரதீப் Nov 20 '15 at 08:12
  • My question is more focused on BOTH improving the READ and damaging WRITE efficiency while the other one is more focus on read only operations. – shawhu Nov 23 '15 at 07:55
  • @vanlightly Although I would agree with you that MySql does have a big difference about using index in a query but in general, I want to answer a more general question. On a general level, creating more indexes will hurt the write speed (update/insert), it's true for both mysql and mssql. And even if it doesn't, I think many people is using mssql and mysql for different projects, they should have a chance to compare and know the difference. At least I do. I don't know if I'm the only one who use both mssql and mysql? – shawhu Nov 23 '15 at 08:00
  • index2 and index3 -- is optimal for an obtuse reason. Both of the parts of the `WHERE` are "ranges". Hence it cannot use both parts of the composite (order_id, buyer_id). Instead, the optimizer can decide whether `order_id>100` is more selective (and use `INDEX(order_id)`), or the other is better. – Rick James Nov 28 '15 at 00:51

2 Answers2

0

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

At the same time index1 would not help much if you filter by buyer_id only (because the BTREE will be structured firstly by the missing order_id as per the index creation statement). You will probably end up with index scan with index1, while having separate indices would still work in that scenario (a seek on index3 is what should be expected).

Dimitar Kyuchukov
  • 310
  • 1
  • 8
0

The performance of an index are linked to its selectivity, the fact of using two indexes, or a composite index must be assessed, in the context of its application or query particularly critical as regards the performance just by virtue of where on the fields as a possible the index reduces the number of rows to process (and put into joins).

In your case, since an order usually only one buyer is not very selective index order_id, buyer_id (pleasant its usefulness to join operations) as it would be more the reverse, buyer_id, order_id for facilitating the search for orders of a buyer

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107