469,647 Members | 1,803 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

pros and cons of indexing

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

Aug 24 '06 #1
3 19391
In a word yes
see http://mysql.com/doc/refman/4.1/en/mysql-indexes.html
mc******@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
Aug 24 '06 #2
mc******@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.
Aug 24 '06 #3
Aggro wrote:
mc******@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.

Aug 25 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Axehelm | last post: by
1 post views Thread by Ronnie Patton | last post: by
reply views Thread by Sniffle | last post: by
5 posts views Thread by Fred | last post: by
3 posts views Thread by Andrea | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.