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?