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 wanna know If I have wanna have index on 2 columns, is it better to index them separately or together? Thanks
Add a comment
|
4 Answers
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.
3 Comments
judda
WHERE colA = ? AND colB = ?; -- can use the index <-- that wouldn't use the index ( colB, colA ) it would need the index ( colA, colB )
Frank Heikens
@judda: WHERE colA = ? AND colB = ?; is exactly the same as WHERE colB = ? AND colA = ?; If MySQL can't use an index on both, it has a serious bug in it's optimizer. EXPLAIN will tell.
judda
I know that with some optimizers you need to index both ways as they are both treated differently. For mysql I'm not 100% sure. The optimizer may swap them around as needed but maybe not. explainextended.com/2010/05/19/… is a pseudo-relevant explanation on indexes and how they work in mysql.
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
Comments
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).
