Search code examples
mysqlindexinginnodb

SELECT/INSERT performance on an indexed column while it's being updated in MYSQL InnoDB

Let's say i have a table with structure like this:

id (int[11]), name(VARCHAR[255])

name column has a b-tree index.

And let's assume that there were queries to this table in this order:

  1. INSERT
  2. SELECT
  3. INSERT

Now, I know that when inserting a new row in an indexed table the index needs to be updated. This takes time. But my questions are:

  1. Would SELECT wait for index to be updated or just search the previous version of table or is there something else to this?
  2. What would happen to the second INSERT? Would it wait for index to be fully updated from previous INSERT or not?

Thank you.

UPDATE

Assume that insert haven't finished updating the index by the time SELECT query is issued.

Solution

  • I think there is some good information here: https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

    and: https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-structure.html

    But generally speaking, an Insert locks the row, but it would not prevent a Select (not even a new Insert). A Select query, relying on an index, that is currently being built due to a new Insert, would not "see" the new row until the index is built.

    Each Insert operation = insert data into row + change affected indexes. The more indexes you have the longer the insert process, the longer it takes before a subsequent SELECT query would find the new row.

    If you are writing code, then you can make your Select wait for the Insert to complete.

    Adding correction and improvement based on comments below

    I don't mean that the statement takes longer to execute, I mean that if you insert a row at time A, and it takes 5 seconds to complete the operation, then a select query the runs at A+2 seconds wouldn't see the row, a select query running at A+6 seconds would see the row.

    To be clearer - the Insert will not delay the Select, unless you configure your DB to have table locks on Insert, but you will not see the Insert in any Select query until the Insert is completed.