Noob Database Index Question

[signature16]

Why is it important to have an Index in my MySQL database.  I am just messing around and PMA has this error on the structure page that says "No Index Defined".

 

 

Can somebody explain why having an Index is so important?  I do understand that it makes searching quicker. 

[utexas_pjm]

I do understand that it makes searching quicker.

 

That's an understatement.  Imagine you have a 500 page programming book, and you're asked to find every occurrence of the word "variable".  If the book did not have an index would have to look at every word from page 1 to 500.  The same holds true with a table in a schema.  Imagine you have a table with n rows and you are looking for all rows where column_a = "variable", if the column is not indexed  the rdbms has to look at every row in the table.  This becomes more and more expensive as a function of n.  If you have an index on that column, just as in the book example, the rdbms knows where to look in order to find the rows where column_a = "variable".

 

Best,

 

Patrick

 

 

[signature16]

So really everything should be indexed?

[utexas_pjm]

It's a tradeoff, the indexes consume space.  You should put indexes on columns that you are selecting by or joining on.