CODEWITHSUNDEEP
mysqlselectindexing
Craig van Tonder
Craig van Tonder

Reputation: 7687

MySQL - Does SELECT * need an index of all table fields?

I would like to know if it is necessary to create an index for all fields within a table if one of your queries will use SELECT *.

To explain, if we had a table that 10M records and we did a SELECT * query on it would the query run faster if we have created an index for all fields within the table or does MySQL handle SELECT * in a different way to SELECT first_field, a_field, last_field.

To my understanding, if I had a query that did SELECT first_field, a_field FROM table then it would bring performance benefits if we created an index on first_field, a_field but if we use SELECT * is there even a benefit from creating an index for all fields?

Upvotes: 0

Views: 3647

Answers (2)

Rashedul.Rubel
Rashedul.Rubel

Reputation: 3584

indexing a mysql table for a column improves performance when there is a need to search or edit a row/record based on that column of that table.

for example, if there is an 'id' column and if it is a primary key; And in that case if you want to search a record using where clause on that 'id' column then you don't need to create index for the 'id' column because primary key column will act as an indexed column.

In another case, if there is an 'pid' column in the table and if it is not a primary key; Then in order to search based on 'pid' column then to improve performance it is better to create an index for the 'pid' column. That will make query fast to search the expected record.

Upvotes: 3

Mureinik
Mureinik

Reputation: 311393

Performing a SELECT * FROM mytable query would have to read all the data from the table. This could, theoretically, be done from an index if you have an index on all the columns, but it would be just faster for the database to read the table itself.

If you have a where clause, having an index on (some of) the columns you have conditions on may dramatically improve the query's performance. It's a gross simplification, but what basically happens is the following:

  1. The appropriate rows are filtered according to the where clause. It's much faster to search for these rows in an index (which is, essentially, a sorted tree) than a table (which is an unordered set of rows).
  2. For the columns that where in the index used in the previous step the values are returned.
  3. For the columns that aren't, the table is accessed (according to a pointer kept in the index).

Upvotes: 4

Related Questions