signature16 Posted May 5, 2007 Share Posted May 5, 2007 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. Quote Link to comment Share on other sites More sharing options...
utexas_pjm Posted May 5, 2007 Share Posted May 5, 2007 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 Quote Link to comment Share on other sites More sharing options...
signature16 Posted May 5, 2007 Author Share Posted May 5, 2007 So really everything should be indexed? Quote Link to comment Share on other sites More sharing options...
utexas_pjm Posted May 5, 2007 Share Posted May 5, 2007 It's a tradeoff, the indexes consume space. You should put indexes on columns that you are selecting by or joining on. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.