MySQL performance Tips
Avoid doing SQL queries within a loop.
MySQL
* MySQL is interpreted from right to left so you should put the most significant limiters as far to the right as possible.
* Only select fields you need, instead of selecting * (everything).
* Don't put things that changes very rarely in the database, instead put it in a global array in some include file.
* Use indexes on the columns in the WHERE clause and on the columns you want to ORDER BY.
* Indexes are great if you search the table alot, but it slows down insertion.
* Use the EXPLAIN command to analyze your indexes.
* If you only want one line as a result from the database you should always use LIMIT 1. This way mysql stops searching when it finds the first line instead of continuing through the whole database, only to find that there weren't any more lines that matched the query.
* If you use $line = mysql_fetch_array($result) you'll get two ways of accessing the columns, $line[0] and $line['columnname']. If you only use the $line['columnname'] you should use $line = mysql_fetch_assoc($result) instead, then there will not be any $line[int index] array.
* Sometimes mysql_free_result() end up wasting more memory than it saves. Check the difference with memory_get_usage().
* Don't ask the database for the same stuff over and over again, save the result.
* Use NOT NULL as default value as much as you can, it speeds up execution and saves one bit.
* Use datatypes that fits your data, not too large. For example, INT can hold values up to 4294967295 unsigned, which is often unnecessarily big. Use MEDIUMINT or SMALLINT where applicable.
* Make use of the default values, only insert values that differs from the default values to speed up the insertion.
Avoid doing SQL queries within a loop.
MySQL
* MySQL is interpreted from right to left so you should put the most significant limiters as far to the right as possible.
* Only select fields you need, instead of selecting * (everything).
* Don't put things that changes very rarely in the database, instead put it in a global array in some include file.
* Use indexes on the columns in the WHERE clause and on the columns you want to ORDER BY.
* Indexes are great if you search the table alot, but it slows down insertion.
* Use the EXPLAIN command to analyze your indexes.
* If you only want one line as a result from the database you should always use LIMIT 1. This way mysql stops searching when it finds the first line instead of continuing through the whole database, only to find that there weren't any more lines that matched the query.
* If you use $line = mysql_fetch_array($result) you'll get two ways of accessing the columns, $line[0] and $line['columnname']. If you only use the $line['columnname'] you should use $line = mysql_fetch_assoc($result) instead, then there will not be any $line[int index] array.
* Sometimes mysql_free_result() end up wasting more memory than it saves. Check the difference with memory_get_usage().
* Don't ask the database for the same stuff over and over again, save the result.
* Use NOT NULL as default value as much as you can, it speeds up execution and saves one bit.
* Use datatypes that fits your data, not too large. For example, INT can hold values up to 4294967295 unsigned, which is often unnecessarily big. Use MEDIUMINT or SMALLINT where applicable.
* Make use of the default values, only insert values that differs from the default values to speed up the insertion.
Comments
Post a Comment