PHP MySQL query optimization

I was just wondering if there is the best way of doing this query?

$searchTerm = explode(' ', $search);
$wall_sql = "SELECT filename, category, name, downloads FROM wallpapers WHERE (pending='开发者_开发问答0' AND mediaType='0') AND ( ";
for($i=0;$i<count($searchTerm);$i++){
    if($i != 0){
        $wall_sql .= " OR ";    
    }
    $wall_sql .= " (category LIKE '%".$searchTerm[$i]."%') OR (filename LIKE '%".$searchTerm[$i]."%') OR (tags LIKE '%".$searchTerm[$i]."%') OR (name LIKE '%".$searchTerm[$i]."%') ";        
}
$wall_sql .= " ) ORDER BY ordernum DESC ";

What this script does it it takes in a search term $search and splits it into an array using the explode function in PHP. I then loop on that array to build up the query. This is the only way I can think to do it, I'm just not sure it's the best way of doing it. Also would any indexes work on this query if so what would they be?

Your query will not be able to use any indexes and using LIKE can give false matches (for example, a search for x LIKE '%red%' will match hired).

If you haven't already considered it, you might want to look at a full text search as this type of search can use a FULLTEXT index.

The way you describe your task you're doing it all right. Indexes are out of the window though if you use LIKE '%search_term%'.

Could refactor the code a bit for readability:

$searchTerm = explode(' ', $search);
foreach ($searchTerm as $s){
    $f[] = "category LIKE '%".$s."%'";
    $f[] = "filename LIKE '%".$s."%'";
    $f[] = "tags LIKE '%".$s."%'";
    $f[] = "name LIKE '%".$s."%'"; 
} 
$wall_sql = " 
SELECT filename, category, name, downloads   
  FROM wallpapers   
  WHERE pending='0' AND mediaType='0'
    AND (".implode(" OR ",$f).")   
ORDER BY ordernum DESC";

The query you give is a normal search query, which may do the job well. If you want to improve performance, you can add a fulltext index and use fulltext search or use a tool like Sphinx. Sphinx indexes your database so that fulltext searches become very fast. However, you need to install and maintain it.

I would create several virtual tables based on the search condition, on join against the tables columns.

Something like

SELECT w.filename, 
       w.category, 
       w.name, 
       w.downloads 
FROM   wallpapers w 
       LEFT JOIN (SELECT 'term1' AS name 
             UNION 
             SELECT 'term2' AS name 
             UNION 
             SELECT 'term3' AS name)  cat 
         ON cat.name = w.category 
       LEFT JOIN (SELECT 'filename1' AS name 
             UNION 
             SELECT 'filename2' AS name 
             UNION 
             SELECT 'filename3' AS name)  f 
         ON f.name = w.filename 
WHERE  w.pending = '0' 
         AND w.mediatype = '0' 
         AND (cat.name IS NOT NULL OR f.name IS NOT NULL)
GROUP by w.id
ORDER  BY w.ordernum DESC 

In this case you would dynamically construct the virtual table values as static hardcoded values (term1,2,3 and filename1,2,3), and you need to add indexes on the following columns

1. (pending,mediatype)
2. (ordernum)
3. (category)
4. (filename)
5. assuming you have primary key id