General questions about index and mysql

Im trying to understand indexes better for when I use Mysql. One issue is Im still having a hard time to determine what type of index I should use such as individual indexes, multi column indexes, covering indexes etc.

One question I have is, is there a general rule to decide what type of indexes to use? When I design my database layout I dont know exactly what all queries will be used until the application is done being built. For one table I could query on one or multiple fields as well as query it for reporting. So if I query a table like so:

SELECT * FROM table1 WHERE field1 = this AND field2 = that GROUP BY field3 ORDER BY field4

Would I create a multiple column index on field1,field3,field3 and field4?

Also what if I have a different query on the same table like:

SELECT * FROM table1 WHERE field1 = this and field3 = that

If I had the multiple column index from the first query will that same index work for the second query since field1 is on the farthest left of the index?

And another question I had was is there a specific order mysql looks for indexes? So for multiple column or a开发者_运维技巧 covering index do I add indexes in order of the where clause? Then anything in group clause then anything in order clause? Or does mysql automatically do this?

Sorry for all the questions, just looking for help on this.

• Engine

First you have to decide which Engine you want to use for a given table

1. InnoDB is preferable (transactions...) but does not offer fulltext index
2. If you need fulltext index, you have to chose MyISAM

(Full text index keeps an index based on words in a column)

• Tables

You have to know that MySQL uses only one index per table maximum in a join. So, don't expect MySQL to combine two indexes of a given table.

• Multi-columns

Chose the order of the column based on the queries, provided that MySQL can use the top of the index if necessary

For instance

  CREATE INDEX myindex ON mytable (col1,col2,col3)

MySQL can use (col1), (col1,col2) and (col1,col2,col3) as index. So to answer your question, your index should be created on

  (field1,field3,field2,field4).

since your two queries needs (field1,field3) and (field1,field2,field3,field4).

When I design my database layout I dont know exactly what all queries will be used until the application is done being built

Correct. Don't build indexes until you know all the queries. It's okay to add, change, alter and remove indexes. Indeed, good designers change the indexes as the use of the software changes.

Would I create a multiple column index on field1,field3,field3 and field4?

Rarely.

If I had the multiple column index from the first query will that same index work for the second query since field1 is on the farthest left of the index?

No.

And another question I had was is there a specific order mysql looks for indexes?

No.

So for multiple column or a covering index do I add indexes in order of the where clause?

No

Then anything in group clause then anything in order clause?

No.

Or does mysql automatically do this?

More-or-less.

Here's the rule.

1. Design the database.

2. Write the queries.

3. Find the most common queries. 20% of your queries do 80% of the work. Focus on the few, slow queries that need indexes.

4. Explain the query execution plans for only the most common queries. There's an EXPLAIN statement for this.

5. Measure the performance of those queries with realistic loads of data. You have to build fake data for this. Some queries will be slow. Indexes may help. Some queries will not be slow.

6. Now comes the hard part. Try different indexes until (a) the explain plan looks optimal and (b) the measured query performance meets your expectations.

You cannot get all queries to be fast.

You do not build indexes for all queries.

Focus on the 20% of the queries that cost 80% of the time.