Index creation time in MySql

I am designing a database in Mysql which will be filled with quite large amounts of raw data. I wanna know that I should define indexes before I insert the data, or I should first insert my data and then create the index? is there any difference? Also I wa开发者_如何学Gonna know If I have wanna have index on 2 columns, is it better to index them separately or together? Thanks

If you are doing a bulk load, my opinion is to not have indexes up front, that will slow the load to constantly write index pages, especially if a larger data set. That being said, after the tables are populated, do a SINGLE statement to build ALL the indexes you expect instead of one-by-one. I learned the hard way a long time ago. I had a table of 14+ million rows and had to build 15+ indexes. Each index was increasingly longer than the last. It appeared each time a new index, it needed to rebuild the pages for the prior. Doing them all at once proved significantly better.

As for multiple column indexes... it depends on how your querying will be performed. If many queries WILL utilize a pair or more of columns in the WHERE condition, then yes, use multiple columns in a single index.

Also I wanna know If I have wanna have index on 2 columns, is it better to index them separately or together?

This depends on your queries. When you have an index (colA, colB) the database can never use this index when you don't use colA in the WHERE condition of your queries. If you have queries WHERE colB = ? then you need an index that starts with this column.

index (colA, colB);
WHERE colA = ?; -- can use the index
WHERE colA = ? AND colB = ?; -- can use the index

This one will fail:

WHERE colB = ?;

But... if you change the order of the columns in the index:

index (colB, colA); -- different order
WHERE colb = ?; -- can use the index
WHERE colA = ? AND colB = ?; -- can use the index

And now this one can't use the index:

WHERE colA = ?;

Check your queries, use EXPLAIN and create only the indexes you realy need.

Insert data first.

If index on two columns, either as combo search or individual would be (under normal circ):

idx_a (fldA + fldB) idx_b (fldB)

regards, //t

Typically when you are doing large inserts of data you will want to index it afterwards, that way it doesn't have to maintain and rebuild the indexes as data is inserted, therefore speeding up the insert process.

The indexing strategy depends entirely on how you intend to query the database. Are you going to be querying them as a set (i.e. have both in the where clause together) or as individuals (i.e. have one or the other in your where clause).