Extremely slow search page load (MySQL and PHP)

I made a simple search box on a page, where a user can type in keywords to look for photos of certain items, using PHP. I'm using an MySQL database. I trim the result and show only 10 to make the loading quicker, but certain set of keywords causes the browser to hang on both IE and Firefox. When this happens on IE, I can see outlines of photos (just the silhouette) beyond the 10 results with an "X" mark at the top right corner, similar to when you load a photo and the photo doesn't exist on a webpage, even though I wrote the code to show only 10 results. The database has over 10,000 entries, and I'm thinking maybe it's trying to display the entire set of photos in the database. Here are some code that I'm using.

I'm using the function below to create the query. $keyword is an array of the keywords that the user has typed in.

function create_multiword_query($keywords) {
// Creates multi-word text search query
$q = 'SELECT * FROM catalog WHERE ';
$num  = 0;
foreach($keywords as $val) {    // Multi-word search
    $num++;
    if ($num == 1) {
        $q = $q . "name LIKE  '%$val%'"; }
    else {
        $q = $q . " AND name LIKE  '%$val%'";}
} 
$q = $q . ' ORDER BY name';
return $q;
//$q = "SELECT * FROM catalog WHERE name LIKE \"%$trimmed%\" ORDER BY name";
}

And display the result. MAX_DISPLAY_NUM is 10.

$num = 0;
while (($row = mysqli_fetch_assoc($r)) && ($num  < MAX_DISPLAY_NUM)) {  // add max search result!
    $num++;
    print_images($row['img_url'], '/_', '.jpg');    // just prints photos               
}

I'm very much a novice with PHP, but I can't seem to find anything wrong with my code. Maybe the way I wrote these algorithms 开发者_开发问答are not quite right for PHP or MySQL? Can you guys help me out with this? I can post more code as necessary. TIA!!

Don't limit your search results in PHP, limit them in the SQL query with the LIMIT keyword.

As in:

select * form yourtable where ... order by ... limit 10;

BTW, those LIKE '%something%' can be expensive. Maybe you should look at Full text indexing and searching.

If you want to show a More... link or something like that, one way of doing it would be to limit your query to 11 and only show the first ten.

Apart from the LIMIT in your query, I would check out mysql full text search (if your tables have the MyISAM format).

Why don't use use MySQL to limit the number of search results returned?

http://dev.mysql.com/doc/refman/5.0/en/select.html

add LIMIT to your query.

you are retrieving all rows from DB (lot of bytes traveling from DB to server) and then you are filtering the first 10 rows.

try

$q = $q . ' ORDER BY name LIMIT 10';

LIKE is slow also according to Flickr(slides 24-26). You should first try to use FULL TEXT indexes instead. If your site still seems slow there are also some other really fast(er)/popular alternatives available:

• sphinx
• elasticsearch
• solr

The only thing that is a little bit annoying that you need to learn/install these technologies, but are well worth the investment when needed.