Query Optimization
Listed In PHP and MySQL » Database Interaction — Viewing Full Tutorial- A database driven site
Introduction
MySQL and PHP driven sites are brilliant things, but when it comes to loadtime it can really slow things down if you're not doing the right thing. Even though MySQL is by far the fastest database program on the planet, you can still cut your loadtime by up to 50% or more by following this tutorial.
Wildcards and over-selecting...
1) Find a query in your site that selects everything from the column.
Does it look like this?
$sql = mysql_query("SELECT * FROM table");
while($row=mysql_fetch_array($sql)) {
echo $row['field1'];
}
If it does, then you're making a big mistake by selecting fields from the database with a wildcard, and another one by selecting everything when you're not going to be echoing everything from the query. You can cut memory usage by more than 50% by rephrasing your query to this:
$sql = mysql_query("SELECT field1 FROM table");
while($row = mysql_fetch_array($sql)) {
echo $row['field1'];
}
I can guarantee you, that if you made this change it would cut the query's memory usage by at least 50% depending on how many fields there were in the table.
As a summary to this section, only select the stuff you will be using from the database and NEVER use wildcards unless necessary!
Column types - are you getting them right?
Are you one of those people who uses VARCHAR and LONGTEXT extensively? If so, then this could be slowing down your queries. For example, if you wanted to store a 150 charachter sentence in a LONGTEXT column, you would be wasting MySQL's time as it expects to find a long blob of text in there, when you could simply use a TEXT or VARCHAR(150) field to do the job more efficiently.
As a summary, when you use TEXT for short bits of data, alternatively use VARCHAR and give it a reasonable limit. This can cut it up to 30%.
