Search code examples
mysqlsqldatabasedatabase-administration

Slow insert statements on SQL Server

A single insert statement is taking, occasionally, more than 2 seconds. The inserts are potentially concurrent, as it depends on our site traffic which can result in 200 inserts per minute.

The table has more than 150M rows, 4 indexes and is accessed using a simple select statement for reporting purposes.

SHOW INDEX FROM ouptut SHOW INDEX FROM output

How to speed up the inserts considering that all indexes are required?

Solution

  • You haven't provided many details but it seems like you need partitions.

    An insertion operation in an database index has, in general, an O(logN) time complexity where N is the number of rows in the table. If your table is really huge even logN may become too much.

    So, to address that scalability issue you can make use of index partitions to transparently split up your table indexes in smaller internal pieces and reduce that N without changing your application or SQL scripts.

    https://dev.mysql.com/doc/refman/5.7/en/partitioning-overview.html

    [EDIT]

    Considering information initially added in the comments and now updated in the question itself.

    • 200 potentially concurrent inserts per minute
    • 4 indexes
    • 1 select for reporting purposes

    There are a few not mutually exclusive improvements:

    Alternatively, change your approach: save the data to a nosql database like redis and populate the mysql table asynchronously for reporting purpose.