1

I just want to know if I'm doing this right. I've searched google for hours already and I can't find a straight answer.

I have a table with potentially a very large amount of inserts and updates. Any time an update is made to a row, the 'modified' column gets changed. My application needs to query this table (along with some joins and filtering), and get paginated results sorted by that 'modified' column. Everything I've read tells me that I need to index the column that is used for sorting. But I've also read many articles telling me that indexing a column that gets updated frequently is a bad idea, because the index needs to be rebuilt every time the value of an indexed column changes.

So what is the proper way to go about doing this? If I index the 'modified' column, will this lead to problems later on when the index becomes very large and it needs to constantly rebuild itself over and over again? If I don't index the 'modified' column, will my pagination queries eventually become so slow that my application becomes unusable? Or is there another solution I haven't thought of?

-EDIT- I am leaning towards using an index on the column. But what I'm still not sure about is what affect a large number of UPDATEs to the indexed column will have on the SELECT queries that use that index. I know that indexes slow down UPDATEs and INSERTs, but I'm not sure if that increased I/O workload from rebuilding the index so many times (EVERY time a row gets updated, which there would probably be dozens of at any given moment) would be a major bottleneck to the server's performance in general. Or is it not as big of a bottleneck as I'm thinking?

  • Does the app really need to paginate by that column? Giving users "standard" pages is not a good idea overall and with such rapid changes it does not seem to have much meaning if the user clicks on "next page" and gets a page from a very different set because things were reordered in-between. Google does not give you "all" pages unless there is only a few of them, Twitter won't give you pages at all. – jkavalik May 18 '16 at 6:11
0

INSERTs have lower impact in affected indexes than UPDATEs, however INSERTs affect all indexes (except for conditional indexes that do not meet the condition).

Indexes are necessary if you need to retrieve data often and quickly. You must weigh the impact of one (indexes in updates/inserts) or the other (full scan searches): you can't have it both ways! An compromise is datawarehousing, which enable fast inserts/updates, infrequent full scans (copying to datawarehouse), and fast searches in not-up-to-date data.

  • Alright fair enough... but just to be clear, do UPDATEs on the indexed column not affect the performance of the SELECT queries at much all, even if there could be dozens of UPDATEs going on at any given moment? That's what I was wondering about. If the index has to keep being rebuilt, if that'll affect performance in more ways beyond just slowing down inserts and updates. – warpio May 18 '16 at 2:32
  • @warpio INSERTS and UPDATES are likely going to add blocks that will interfere with SELECTS and other UPDATES and INSERTS. However, single row inserts and updates (i.e. not within a transaction with multiple operations) would lock rows/tables very shortly, and thus cause a minimal interference. You can reduce the impact of UPDATE/INSERT locks by SELECTing in a READ-UNCOMMITTED isolation level transaction which would allow for ghost reads. – Ziggy Crueltyfree Zeitgeister May 18 '16 at 2:48
  • 1
    okay, that definitely cleared things up for me. thanks – warpio May 18 '16 at 3:00
  • Why "read uncommited"? InnoDB uses "repeatable reads" by default and with that there is no blocking of reads by concurrent updates, no way for (non-locking) reads to interfere with any locks. – jkavalik May 18 '16 at 6:07
2

Surely READ UNCOMMITED is a recipe for disaster? It's not called "Dirty Read" for nothing!

Everything I've ever read about Oracle says so. In particular, Tom Kyte's "Oracle Database Architecture" which flat out states that Oracle refuses to provide that level and that is a good thing!

I saw the MySQL tag on this post, but this is universally applicable to databases in general IMHO.

Of course indexes slow down INSERTs and UPDATEs. Also, naturally, they will speed up SELECTs.

You can't know in advance performance will be affected for either case except by extensive experience and/or testing. What to do about this is a business decision, not a technical one.

Your question cannot be reasonably answered without far more information. Your UPDATE queries? Your SELECT queries? Your transaction rates? Your disk setup? How are your iostat results? Your entire sar statistics? Is your system humming or do you experience bottlenecks/slowdowns?

How is your budget? How much of a delay in reporting can the business tolerate? My first instinct in your case is to go for an offline data-warehousing solution with a 24 hour lag (DW built overnight - using some form of replication).

This is from my experience of how OLAP reports can cripple a running OLTP system. However, I cannot say for sure without far more information.

1

(Assuming InnoDB...)

The data and the PRIMARY KEY are in one BTree. Each secondary INDEX (including UNIQUE indexes) is in its separate BTree.

An update requires modifying a record in the data BTree. If that also involves updating a column that is in any index it requires, effectively, a DELETE from that BTree plus an INSERT somewhere else in that same BTree.

On the one hand, you can argue that DELETE and INSERT are designed to be efficient, and we do it all the time. On the other hand, you can see that modifying an indexed column is a 'lot' of work under the covers.

Bottom line: It is a tradeoff between SELECT efficiency and UPDATE cost. (You have not provided enough info for us to give a simple yes/no.)

(More)

After deleting a row from one spot in the BTree and inserting a new row in (perhaps) a different block, the blocks may need some splitting, etc. But, even in the worst case, rebalancing does not require regenerating the entire index. I think it is limited to a small multiple of LogN -- for example, in the rare case when it needs to do one block split for each level all the way up to the root node. A billion rows may have only 5 levels, so we are not talking a huge overhead.

Furthermore, for non-UNIQUE indexes, the index update is buffered in the "Change Buffer". This collects index updates; later they are 'batched' together to for efficient storing to disk. Meanwhile, if the index is needed, the system looks both in the Change Buffer and the actual on-disk (but cached in the buffer_pool) index. The Change Buffer is allocated as a percentage of the buffer_pool (cf innodb_change_buffer_max_size, which is usually left at 25).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.