MySQL indexes
Posted: Fri Oct 23, 2009 11:59 amThough the transformation of MySQl from a "toy" to a full-fledged, acid compliant enterprise quality RDBMS continues, some things never change. One of the things that always struck me as odd is the way MySQL uses indexes. First, let me say, when I first discovered that proper indexing was just as or more important than proper schema design - that was a real eye opener. I went from no indexing at all to "index everything" to strategic indexing very quickly as my sought to expand my knowledge on MySQL.
While many development operations - especially larger ones - have resources dedicated to RDBMS design and management, it is still strongly desired that developers reaching the intermediate stage have more than a cursory knowledge of how databases work and how they should be designed. Developers reaching the senior level usually have advanced knowledge of database design principles. While neither may be actively involved in the setup and maintenance of a MySQL server, it is they who write the queries and often come up with initial designs for databases and tables. Slow and inefficient queries are a common bottleneck in any application, large or small. Query inspection and optimization is and should be part of any good QA process.
But in my early years as I was pouring over the MySQL documentation in an effort to understand precisely how MySQL used indexes I realized... everything I believed... how I THOUGHT it would work... was wrong! Having a table with no indexes, with rare exception, was begging for big trouble. In fact, the only way you could REALLY get a good understanding of how your table needed to be indexed was to design it without indexes and write all your queries. Once you had your query library solid you could come back to the table(s) and index - the main goal being that you wanted to retrieve as much information without touching the table itself, you only wanted to scan indexes. Scanning tables was bad. Queries that caused full sequential table scans take forever and can even cause a fast hard drive to thrash.
Some food for thought. Those tables are more than just storage areas. They CAN be made to sing. You create the music with indexes! Learn them. Know them. Live them!
Mike
While many development operations - especially larger ones - have resources dedicated to RDBMS design and management, it is still strongly desired that developers reaching the intermediate stage have more than a cursory knowledge of how databases work and how they should be designed. Developers reaching the senior level usually have advanced knowledge of database design principles. While neither may be actively involved in the setup and maintenance of a MySQL server, it is they who write the queries and often come up with initial designs for databases and tables. Slow and inefficient queries are a common bottleneck in any application, large or small. Query inspection and optimization is and should be part of any good QA process.
But in my early years as I was pouring over the MySQL documentation in an effort to understand precisely how MySQL used indexes I realized... everything I believed... how I THOUGHT it would work... was wrong! Having a table with no indexes, with rare exception, was begging for big trouble. In fact, the only way you could REALLY get a good understanding of how your table needed to be indexed was to design it without indexes and write all your queries. Once you had your query library solid you could come back to the table(s) and index - the main goal being that you wanted to retrieve as much information without touching the table itself, you only wanted to scan indexes. Scanning tables was bad. Queries that caused full sequential table scans take forever and can even cause a fast hard drive to thrash.
Some food for thought. Those tables are more than just storage areas. They CAN be made to sing. You create the music with indexes! Learn them. Know them. Live them!
Mike