Extremely slow search page load (MySQL and PHP)

Question

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!!

Hey everyone, the LIMIT 10 does not seem to work with the browser freezing. It displays 10 results but the search result page still extends down with empty boxes marked with X's beyond the 10th result. I'm verifying that the LIMIT 10 statement is indeed being included into the query. Could there be another reason? BTW, the query that freezes the browser only returns 16 results total. — musicliftsme, Jun 29 '11 at 00:22
I think something is not right with the while loop. If I change it to while ($num == 0) to run display just 1 result, it hangs in the same manner as described originally. I will look into this further. — musicliftsme, Jun 29 '11 at 00:30
HM.. I tried out a for loop with limited iterations and using mysqli_fetch_row() instead, but they are not the problem apparently. What freezes my browser are certain rows from the DB. For example, if I do a search that returns item "A", this query will freeze my browser no matter what was typed into the search box. Weird.. Search for item "A" works without a problem within phpMyAdmin. — musicliftsme, Jun 29 '11 at 00:56

Mat · Accepted Answer · 2011-06-28T21:48:00.323

5

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.

edited Jun 28 '11 at 21:48

answered Jun 28 '11 at 21:31

Mat

188,820
38
367
383

Is there a simple way to tell if the limit has been met without running two queries with and without LIMIT and comparing returned values? I'd like to inform the user if the limit was reached. – musicliftsme Jun 28 '11 at 21:41
Search this site for "[mysql] pagination", you'll find a lot of info on this type of thing. [MySQL pagination without double-querying?](http://stackoverflow.com/questions/818567/mysql-pagination-without-double-querying) has some info. – Mat Jun 28 '11 at 21:45

score 3 · Answer 2 · answered Jun 28 '11 at 21:37

3

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

answered Jun 28 '11 at 21:37

jeroen

88,615
21
107
128

score 2 · Answer 3 · answered Jun 28 '11 at 21:32

2

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

answered Jun 28 '11 at 21:32

Grambot

4,057
4
26
42

score 1 · Answer 4 · answered Jun 28 '11 at 21:32

1

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';

answered Jun 28 '11 at 21:32

fdaines

1,126
8
12

score 0 · Answer 5 · answered Jun 29 '11 at 01:53

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:

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.

Extremely slow search page load (MySQL and PHP)

5 Answers5