pros and cons of indexing

[mcyi2mr3]

can anyone explain to me what the pros and cons are of creating indexes
on mysql tables. do they increase query speed? and if so at what cost?

im thinking of using them for a large table i have which contains two
text fields and may run into millions of records, is that advisable?

regards

marc

[davie]

In a word yes
see http://mysql.com/doc/refman/4.1/en/mysql-indexes.html
mc******@google mail.com wrote:

can anyone explain to me what the pros and cons are of creating indexes
on mysql tables. do they increase query speed? and if so at what cost?

im thinking of using them for a large table i have which contains two
text fields and may run into millions of records, is that advisable?

regards

marc

[Aggro]

mc******@google mail.com wrote:

can anyone explain to me what the pros and cons are of creating indexes
on mysql tables. do they increase query speed? and if so at what cost?

If indexes are used, writing to table (for example inserting rows) is
slower, because in addition to just writing the data, MySQL needs to
handle the index file also. This is an issue usually only if you need
very fast inserts or you need to insert thousands of rows at the same
time and fast.

They might increase query speed or they might not. Basic rule is that if
you are searching some specific key in a table, using indexes in that
field will make query faster, but if you have index in some other field,
it won't make a difference.

For example if you have a table with 1000000 rows in it, and you want to
make this query:

select id,name from table_x where id=123123;

You will get a lot faster results if you have index in the id field.

On the other hand, if you don't have index in the id, but only in the
name field, the query will be as slow (or even few cpu ticks slower)
than it would be without indexes at all.

[lark]

Aggro wrote:

mc******@google mail.com wrote:

>can anyone explain to me what the pros and cons are of creating indexes
on mysql tables. do they increase query speed? and if so at what cost?

If indexes are used, writing to table (for example inserting rows) is
slower, because in addition to just writing the data, MySQL needs to
handle the index file also. This is an issue usually only if you need
very fast inserts or you need to insert thousands of rows at the same
time and fast.

They might increase query speed or they might not. Basic rule is that if
you are searching some specific key in a table, using indexes in that
field will make query faster, but if you have index in some other field,
it won't make a difference.

For example if you have a table with 1000000 rows in it, and you want to
make this query:

select id,name from table_x where id=123123;

You will get a lot faster results if you have index in the id field.

On the other hand, if you don't have index in the id, but only in the
name field, the query will be as slow (or even few cpu ticks slower)
than it would be without indexes at all.

to make things a bit faster as far as returning control is concerned,
you can also use query modifiers. these will help distribute server load
more effectively.