This feels like a stupid question, but I was unable to find the
obvious answer.

I have a simple SELECT query, like this:

SELECT id, name, street, city, state, zip FROM table WHERE
type='whatever';

Should I create an index with all of these columns in it, or just the
"type" column?

If it should contain all of the columns, then where does the "type"
field need to be placed? Beginning of the index, end, other?

On 8/11/2011 9:50 PM, jwcarlton wrote:
If you create an index, 'type' must be the first field in the index for
MySQL to be able to use it in a WHERE clause.

Whether you add additional columns or not is up to you.

If you do add all the columns, the data will be available in the index,
and MySQL won't have to fetch the row. OTOH, the index will be much
bigger, so it will take a bit longer for MySQL to read from the disk on
every request using that index.

Additionally, adding more columns to the index will mean the index will
need to be resorted every time data in that index changes - which
includes all INSERT and DELETE operations (which will take longer
because the index is bigger) and any UPDATE affecting any column in the
index.

Personally, I keep columns in an index limited to what is needed for
that index. I don't add extraneous columns.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Is that the *ONLY* query you ever use?

Generally: create indexes for columns you use in WHERE clauses
(especially WHERE clauses in heavily-used queries), or on columns
you need to have unique. Don't throw in columns just for the heck
of it.

If one column is used in a query, it should be *first* in
the index for the index to be useful.

If two columns are used in a query (for exact match), either can be first.

If you have two queries, one using just last_name and the other
using both first_name and last_name, then an index on (last_name,
first_name) can serve for both.

That is virtually never true, unless all of the columns are needed
to uniquely select a row. You might run into this with a many-to-many
relationship like "person owns car" and the new table has two
columns, a person and a car. Several people can own a given car,
and a person can own more than one car. You may want a unique index
on (person, car) or (car, person) because a person can't own the
same car twice. Which is suitable depends on whether other queries
are more likely to search it for a person (find all the cars this
guy owns) or for a car (find who owns this car).