Practical Tips on Reducing Load of MySQL Queries
Alot of PHP/MySQL programmers out there , specially the ones just starting out make some mistakes while using queries. The ideas i am going to outline aren’t just limited to MySQL , they can be equally applied elsewhere as well. We need to keep in mind the fact that MySQL will use more RAM and CPU than it should if inefficient queries are written.
- For instance one common mistake is to use “SELECT * from table1” , unless you don’t need to use all of the fields i strongly suggest against it. Use something like “SELECT field1,field2 from table1“
- Make sure you know what you are doing when using JOINs. Use “explain” to see how many rows are actually being scanned for the query to execute and bring the result. A lot of times its better to break the query up into several queries. JOINS ARE HEAVY!
- I have seen people use order by clause in queries where they need to get just the count of the records. That’s totally unintelligent. Order by clause uses up RAM as well to sort the data. Avoid it where it can be avoided.
- Saving images in DB is probably not a really good idea after all. Atleast I think so. If you can , doesn’t mean you should.
- Integer primary keys are faster than making username the primary key or something similar.
- Fixed length fields are processed faster than variable length fields.
- Use indexing wisely. Indexing helps in query the database faster BUT insertion and updates are slower.
- Sometimes it better not to create text book database structure aka normalization , sometimes a little redundancy can help minimize number of queries AND/OR load on database, specially when dealing with large databases.
- Keep in mind that in the loop while($array=mysql_fetch_array($queryresult)) , the transaction will keep the query open until the end of the loop. So if there is some heavy processing happening inside the loop on the result data, it is probably a good idea to first run the query loop and store the data you want to process in arrays and then later on process on the array. I find this technique helpful on many occasions.
- Using mysql_pconnect doesnt always help .
- Using mysql_free_result is probably a good idea.
- mysql_insert_id is a useful function to get newest primary key of the record , rather than using “….order by id desc limit 1″
- If you know you will be using some mysql field data alot , like the username of the logged in user , its always a good idea to store it in session , rather than banging on mysqls door again and again for it.
- ENUM field type is very useful , just like fixed length fields. Data processing on ENUM fields is quite fast.
- Using count(*) in queries instead of mysql_num_rows is faster i believe.
- I believe if you are storing signup dates etc , its probably a good idea to have the field as integer instead of datetime and put in mktime() value. This only applies to current and future dates. In cases where past dates are possible datetime should be used. As you never know if that date can be earlier than 1970. Storing mktime gives you faster processing and manipulation of data is easier as well in PHP with the date() function.
- Dont use fulltext unless you really have to.
- And finally DO USE PRIMARY KEYS , dont be affraid to use them , they can help you down the road. For instance “DELETE from table1 WHERE id = 1″ and “DELETE from table1 where firstname=’php’ and lastname=’rox’” , the first query i reckon will run faster.
Those are all i can think of right now. Please remember i am not an expert on MySQL , the above are based on professional experience with PHP and MySQL. Another thing to remember here is , all of these points might seem insignificantly small but when piled up together. They can cause major havoc. Specially when large databases are question.
Hope the tips can really help someone.
