CODEWITHSUNDEEP
phpmysql
webnoob
webnoob

Reputation: 15934

Indexing columns but keeping Inserts fast

I need to index most of the columns in one of my tables because it is being used for log searching by users with lots of filters.

This same table has about 2-3 inserts per second. I know indexing affects inserts on a table so will this be a problem?

I am using the latest version of MySQL (?) with PHP 5.

Thanks.

Upvotes: 1

Views: 258

Answers (3)

Johan
Johan

Reputation: 76567

There are a few tricks you can use to speed up inserts

Index definition

  1. Use an integer autoincrement column as a primary key
  2. Avoid unique keys
  3. Avoid foreign keys if not necessary
  4. Only use integer fields for foreign keys if you must use them
  5. Use partial keys for text/string columns
  6. Do not use multicolumn keys
  7. Consider the cardinality of your indexes; if there are 1,000,000 rows, but only 5 unique values, using a key does not make much sense, a full table scan is faster.

Inserts

  1. Bundle your inserts if possible, inserting 5 rows in one statement is considerably faster than doing 5 individual inserts, so if you can store your log in a non-indexed temp table for 5 minutes and then doing an insert-select from that into your indexed table you will gain a lot of speed.
  2. Use insert delayed.
  3. You can disable index updates before a bulk insert and enable the index updates afterwards. Do an ALTER TABLE ... DISABLE KEYS before the bulk insert and ALTER TABLE ... ENABLE KEYS after the bulk insert. This will delay the update of all non unique indexes until after all the inserts are done.
  4. Consider using LOAD DATA INFILE; it's the fastest insert mechanism MySQL has.

Maintenance

  1. Use SHOW INDEX on your tables to check the stats and look for opportunities for improvement.
  2. Run OPTIMIZE TABLE periodically on your MyISAM, InnoDB and Archive tables.

Choice of engine

  1. Test to see if using MyISAM as a storage engine will improve performance.
  2. Under some loads the archive storage engine can do faster inserts, test to see if it works faster for you. Archive will only allow a single primary index though, so for you this is not an option.

Check out the answers to the following questions on SO:
How to optimize mysql indexes so that INSERT operations happen quickly on a large table with frequent writes and reads?
are MySQL INSERT statements slower in huge tables?
Mysql InnoDB performance optimization and indexing

Upvotes: 4

bobflux
bobflux

Reputation: 11581

If you only got 2-3 INSERTs per second, there is will be no problem with your indexes (unless there is a FULLTEXT index).

Upvotes: 0

Will A
Will A

Reputation: 24988

All I can suggest is to try it and see how it affects performance. Ensure that you only index those columns that will be used in searches, and only include more than one column in the index if "it makes sense" to do so - remember that in order for an index to be useable, the first column must be referenced in a WHERE, JOIN...ON clause, or ORDER BY - any latter columns mentioned in the index will not be used unless this condition is met.

Upvotes: 2

Related Questions