CODEWITHSUNDEEP
mysql
user900889
user900889

Reputation: 75

Why does one index work and the other does not?

I beg your pardon if this is an exceedingly stupid question, but I am new to MySQL. I have a fairly big database (70 GB) with a simple structure (just one table) which I intend to use for fast retrieval of records. As you can imagine, grepping a 70GB flat file is no fun.

In my first attempt, I created a table (nothing fancy, 1 ENUM, 4 unsigned INTs and 2 FLOATs). Since data retrieval without an index was slow, I created an index after the table had been filled with data, by using something like 

create index myid_index on mytable (myid) using btree;

and after doing that, everything worked fine. Retrieval speed was ok for me. Then I noticed that I did a mistake with the imported data so I started over. This time, I defined the table with an index before actually filling the table. This is how I understood things should be done. The command was something like

create table mytable ( ... , myid INTEGER unsigned, ..., index USING BTREE (myid))

When I did this, an index was created, but it does not seem to work, i.e. the retrieval is way to slow. Apparently the index is not used.

The indices of both procedures have the same size and are displayed in the 'explain mytable' view totally identically. The only difference is that the index only works for me if it is defined after all of the data have been imported.

What am I doing wrong ? While we're at it, a 2nd question: When I define the index with the 'create index' command, i have to give it a name. What is this name used for? I don't have to specify it during the query, do I ?

Upvotes: 2

Views: 89

Answers (1)

RandomSeed
RandomSeed

Reputation: 29749

tl;dr: since the below did not address the issue, more answers are welcome

Actually, it is better to mass-import your data first, then create an index. By creating the index before the import, you forced MySQL to recompute the index after inserting each and every row (this is not exactly what happens in fact, but it is conceptually close). Conversely, if you create the index after import, then the index needs to be constructed only once.

As a result of the index being updated many times during your data import, fragmentation probably occured, hence the degraded performances. I would recommend issuing an OPTIMIZE TABLE [the_table] after your mass insertion.

As for your second question, specifying a name for an index is optional. MySQL creates one by default if you omit the clause. I would recommend specifying it explicitely though, it makes retrieval easier (eg. you need to specify the index name in a DROP INDEX statement).

Upvotes: 3

Related Questions