This is for the beginners out there with databases. It won't contain anything fancy or in depth, but there are some basic things you should keep in mind when working with MySQL databases. For a more in depth treatment, check out the mysql optimization page at http://dev.mysql.com/doc/refman/5.0/en/optimization.html
SELECT statement optimizations
-The more GRANTS you have on a user performing a select, the more overhead you'll have. Things like table level and column level grants will make your queries run slower.
-If youre searching on a specific column a lot, add an index to it (more on indexes below).
-If you have a large table and you have an OR statement in the WHERE clause, try changing the query to use a UNION instead. With an OR statement, indices lose their effectiveness, while the UNION allows them to retain their extra speed.
-Be careful with DISTINCT, GROUP BY, and similar functions. On large data sets, they can slow your query down to a crawl. If you must use them, consider using the LIMIT function to only bring back as many results as you need, as the query stops once the limit is reached.
INDEXES
Indexes speed up queries on certain rows. If you have a large table and you always search on the same column, adding an index can speed it up. However, dont add indexes to every column, as they carry some disadvantages. First, it adds extra storage for each one. A column with an index will take up more disk space than an indentical column without one.
Second, and probably more importantly, indexes slow down insertion time. If you have a lot of inserts on a table, be careful with using indexes.
Database Design
One of the most important things in making a scalable application (web or otherwise) is database design. While the specifics are well beyond the scope of this post, definately take the extra time to design your database well at the start. Rushing through can cause you problems down the road as your database grows in size and you may end up having to redesign it after release.
So those are just a few quick and simple tips that Ive run across that I hope will at least get you started. If I made any mistakes or you have something to add, just post and Ill try and edit this post to keep it up to date.
SELECT statement optimizations
-The more GRANTS you have on a user performing a select, the more overhead you'll have. Things like table level and column level grants will make your queries run slower.
-If youre searching on a specific column a lot, add an index to it (more on indexes below).
-If you have a large table and you have an OR statement in the WHERE clause, try changing the query to use a UNION instead. With an OR statement, indices lose their effectiveness, while the UNION allows them to retain their extra speed.
-Be careful with DISTINCT, GROUP BY, and similar functions. On large data sets, they can slow your query down to a crawl. If you must use them, consider using the LIMIT function to only bring back as many results as you need, as the query stops once the limit is reached.
INDEXES
Indexes speed up queries on certain rows. If you have a large table and you always search on the same column, adding an index can speed it up. However, dont add indexes to every column, as they carry some disadvantages. First, it adds extra storage for each one. A column with an index will take up more disk space than an indentical column without one.
Second, and probably more importantly, indexes slow down insertion time. If you have a lot of inserts on a table, be careful with using indexes.
Database Design
One of the most important things in making a scalable application (web or otherwise) is database design. While the specifics are well beyond the scope of this post, definately take the extra time to design your database well at the start. Rushing through can cause you problems down the road as your database grows in size and you may end up having to redesign it after release.
So those are just a few quick and simple tips that Ive run across that I hope will at least get you started. If I made any mistakes or you have something to add, just post and Ill try and edit this post to keep it up to date.
