MySQL: how to create index on large table?

matjaz

i have large table with 5+ mio records, 1GB size. when i try to create an index on one of the fields, it takes mysql 2 hours and then it finished with 12! same indexes???

can you tell me what is wrong?

besides that it takes LOTS of time to create one additional index. is it possible it takes one hour? isnt this the fastest DB around?

help!
i must create this index!!! 😕

fandelem

A lot of this depends on the actual server it resides on, more than just the speed of the database...

matjaz

4 pentium 3 processors
4GB ram
RAID whatever blah blah

mattr

Something tells me you have:
1) a poorly designed database
2) a poorly designed application

Indexes are physical structures used to aid querying, generally you create them once and you are done with it.

The fact that you are forced to create many idexes often seems to point to a poorly designed database.

matjaz

hmm, i dont really agree. i just have lots of fields in WHERE part and as far as i know i need indexes for these fields.

you maybe misunderstood ~ i said i want to create another index, so application doesnt count here. i have the database and mysql and i want to add an index. just this.

[deleted]

Indexes are really just abbreviated versions of the original table which are sorted and optimized.

So yes, it can take a long time.

"i just have lots of fields in WHERE part and as far as i know i need indexes for these fields."

That depends on which columntypes you use, and how you compare them.

Some queries will not use indexes at all, like:

SELECT * FROM tabel WHERE column LIKE '%something%';

Also not that the more indexes you have, the slowe your updates andinserts will be.

do NOT create indexes on every column you use in the WHERE, because then mysql will spend all it's time loading many indexes.

matjaz

it is a search engine project. most of queries are SELECTs but its true - there are a lot of INSERTS as well.

i was watching the speed of queries and in some cases it took MySQL 30 seconds to return results when i had no index on fields used in WHERE. when i added the missing index, it took it 2 seconds.

about that problematic index: youre saying it is normal to take it so long? so i should just click and wait for hour or more?

[deleted]

"about that problematic index: youre saying it is normal to take it so long? so i should just click and wait for hour or more?"

There really isn't any problem with the index, it just takes a long time to create.

This is not really a problem because you never create indexes while the database is in use.

matjaz

obviously you do! :-x
if you forgot like i did.