1

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.

webnoob
  • 15,747
  • 13
  • 83
  • 165
  • lastest MySQL what? 5.0, 5.1, 5.5, 5.6? Which storage engine MyISAM, InnoDB, Archive. BTW I recommend using blackhole for storage, very fast inserts :-) – Johan May 01 '11 at 20:36

3 Answers3

4

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

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
2

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.

Will A
  • 24,780
  • 5
  • 50
  • 61
  • Sorry what do you mean by `the first column`? I will only be indexing columns I use the in the Where / order by if that is what you mean? – webnoob May 01 '11 at 19:17
  • 1
    I think Will is referring to using composite or multi-column indexes – Mark Baker May 01 '11 at 19:41
  • @Mark, @Webnoob - yes, exactly that. I've seen a several instances of newbies to databases assuming that the order of fields in an index doesn't matter. – Will A May 02 '11 at 17:43
0

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

bobflux
  • 11,123
  • 3
  • 27
  • 27