Best way to Index large tables in MySQL database

[JoeKid09]

Hi All,

I'm very new with MySQL database tables. I'd like to be able to index columns in large tables (40gb)size. I was wondering if youknow the best way to do so, please let me know if further information is needed. Thanks in advance!

Joe

[Rabbit]

I'm not sure what you mean by best way. The size of a table doesn't really dictate what and how things get indexed. It's the usage that will mostly dictate what indexes you create.

[JoeKid09]

The table will be supplying data to multiple users, therefore there will be a very intense query execution. So for example, if there is a table of 50 fields, and I want to be able to create index on 10 of the columns. What would be the most efficient sql script to achieve this?

[Rabbit]

If you want to create 10 indexes, then you just create them. There's not a whole lot of efficiency in how you tell the database to create that index.

What I mean to say is that how you tell the database to create an index doesn't have much bearing on how that index is then later used in querying the indexed field.

What's more important is the choice of indexes. Not how you tell it to create it. And the choice is determined by usage.