#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171

    How does MYSQL create indexes


    There is a table with 10 million records. I decided to experiment creating indexes.
    Code:
    CREATE INDEX price_index ON products_table (price);
    It's been 1.5 hours and still loading. It made me curious!

    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?

    Thank you
  2. #2
  3. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Finished after 5460 seconds
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    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.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by E-Oreo
    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?
  8. #5
  9. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    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.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  10. #6
  11. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    If you are using MyISAM engine which is the simplest it's simply:
    Originally Posted by TheManual
    Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.
    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.
    Ref: http://dev.mysql.com/doc/refman/5.6/...ge-engine.html

    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.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo