How can I speed up a MySQL product search result?

Question

I am doing a query on an mysql database based on typed data sent from a jquery autocomplete function. It works fine but take several seconds (5-6+) to get the returned suggestions. There are around 200,000 product entries in the database currently which is expected to go to well over a million entries. How can I speed this thing up, as once more entries are added, it stands to reason that the results would likely never show before someone has already typed in the full search query, which does not benefit them at all.

The MySQL tables are:

CREATE TABLE `products` (
  `id` int(12) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `description` text NOT NULL,
  `category` int(12) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `category` (`category`)
) ENGINE=MyISAM;

CREATE TABLE `products_pics` (
  `id` int(12) NOT NULL auto_increment,
  `product_id` int(12) NOT NULL default '0',
  `thumb` blob NOT NULL,
  `image` longblob NOT NULL,
  `type` varchar(6) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `product_id` (`product_id`),
) ENGINE=MyISAM;

The PHP code for the search and result return is:

if(isset($_REQUEST['queryString'])) {
 $queryString = addslashes($_REQUEST['queryString']);
 if(strlen($queryString) > 0) {
  $result = mysql_query("SELECT * FROM products WHERE name LIKE '%" . $queryString . "%' ORDER BY name LIMIT 8", $db);
  if(mysql_num_rows($result) > 0){
        while($myrow = mysql_fetch_array($result)){
        $query = mysql_query("SELECT id FROM products_pics WHERE product_id='$myrow[id]' LIMIT 1", $db);
        if(mysql_num_rows($query) > 0){
        echo '<a href=""><img src="/ajax/search_images.php?id=' . $myrow[id] . '" width="55" border="0" alt="" />';
        }
        else {
        echo '<a href=""><img src="/images/items/no-img.jpg" width="55" border="0" alt="" />';
        }
        $name = stripslashes($myrow[name]);
        if(strlen($name) > 35) { 
         $name = substr($name, 0, 35) . "...";
        }             
        echo '<span class="searchheading">' . $name . '</span>';
        $description = stripslashes($myrow[description]);
        if(strlen($description) > 80) { 
         $description = substr($description, 0, 80) . "...";
        }
        echo '<span>' . $description . '</span></a>';
       }
       echo '<span class="seperator"></span>';
   } 
  }
 } 

Thanks again guys, any help is appreciated.

Answer 1

Doing LIKE %keyword% is very slow. Because you are doing autocomplete you could discard the first %, doing: LIKE keyword%.

That will speed up your query dramatically.

Also, try to execute your query in MYSQL like 'EXPLAIN <>'. This will provide you with info on how MySQL executes your query and where you might optimize.

Another option might be to use MyISAM's FULL TEXT SEARCH capability: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html