View Categories

What Is MySQL Index Usage & How To Use It

  • Feb 20, 2012
  • 0
  • by A2 Marketing Team

The MySQL database has been a cornerstone of modern software for over a decade. Whatever the purpose of your site, there’s a good chance you’re using one. Despite MySQL’s popularity, a thorough understanding of its inner workings can be difficult to master. For those users who have dabbled but have not yet become wizards of this relational database, there is a key feature that is worth discussing; the index.

An index allows MySQL to more quickly perform a query involving the indexed column. By default, MySQL will index primary keys since it is assumed that these will be commonly queried (and indeed it is a good idea to design your tables this way). The needs of your website, however, may dictate common queries on other columns. In this case, it is probably a good idea to tell MySQL to index those as well.

The index is not without cost. Obviously the larger your database, the larger the index will be. While it might be tempting to tell MySQL to index every conceivable column you may perform a SELECT .. WHERE query on, in the end your database may end up using so much memory that you find the performance has become unbearably slow.

So where is the happy middle ground? A good guideline is to have indexes only on those columns which are constantly being queried, either unavoidably by mission critical code, or commonly by users. For example, you may find that it is common for users to perform a search on your site based on keywords, but less common based on an author’s name.

In any case, modification of your MySQL database should only be done by someone familiar with MySQL to avoid any potentially catastrophic mistakes. Visit our MySQL Hosting page to learn more about this powerful relational database.