April 30th, 2013, 03:30 AM
How does MYSQL create indexes
There is a table with 10 million records. I decided to experiment creating indexes.
It's been 1.5 hours and still loading. It made me curious!
CREATE INDEX price_index ON products_table (price);
1 - How long is it gonna take? Should I restart the server?
2 - How does the process of creating indexes work? Is it going through every record and do something special there? Is it orgonizing the data? Or is it simply adding index in to specific locaitons on file?
April 30th, 2013, 03:34 AM
Finished after 5460 seconds
April 30th, 2013, 10:23 PM
Yes, it goes through every record and does something, so the time it takes to add an index does correspond to the number of records in the table. However, it's not something that's easily predictable as far as I know. Restarting the server in the middle of it definitely is not going to make it go any faster.
Exactly what it does for each record depends on the index type. It is organizing the data in some way, usually a tree or a hash map.
Where are they stored?
Originally Posted by E-Oreo
The storage location and format depend on the database engine type. I don't know the specific details about the formats for any of the engines, however most are stored on the disk somewhere.
If you are using MyISAM engine which is the simplest it's simply:
All these files are located in a directory which is the Database Name. So a database in MyISAM is simply a new directory that contains three files per table that you have created.
Originally Posted by TheManual
If you on the other hand are using InnoDB then both indexes and the table data (which in fact is also stored as part of the primary key index) is stored in the ibdata1 file(s) (or whatever you call your ibdata files).
Google for MyISAM and InnoDB together with mysql manual and the mysql internals if you want to read up on all details, they are a lot of useful information there.