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

In a word yes
see http://mysql.com/doc/refman/4.1/en/mysql-indexes.html
mcyi2mr3 [at] googlemail.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

mcyi2mr3 [at] googlemail.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.

Aggro wrote:
> mcyi2mr3 [at] googlemail.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.