Selecting Specific Results or Filtering With PHP

Hello,

I have a fairly large database and I give my visitors a search option so that only the matching results are displayed. I have thought of two ways I can use to select the results that match their query, one consists of using a "complex" sql query and the other method consists of fetching all the results and using PHP to display only the ones that meet the search criteria. Here is a simple example:

$que = mysql_query("SELECT * FROM models WHERE weight='100'");
While($row = mysql_fetch_array($que)){
       echo $row['models_name'];
       echo "<br />";
}

Code (markup):

$que = mysql_query("SELECT * FROM models");
While($row = mysql_fetch_array($que)){
       If($row['weight'] == 100){
              echo $row['models_name'];
              echo "<br />";
       }
}

Code (markup):

As you can see both operations have the same result.

I was wondering which would use up less resources on my server and produce a faster result?

thanks,
Daniel

 

I'd always go the SQL route when possible. If the search uses a list of predefined options (not freeform text entry) and the data doesn't change too often (and there aren't too many queries used...), enabling query caching could have a reasonable impact on query times.

 

Of course, One must select SQL one!!

 

SQL for definate - processing will be far quicker!!!

 

Definitely filter as much as you can through the sql query.

 

Your second code is no better than storing the data in a flat file and using PHP to parse through it.

There's more to databases than just storage (obviously). Your SQL tables have indexes that will perform searches in O( ln(n)/ln(2) ) time or better, where n is the number of rows. So lets say you have 100 rows and you do a query. With SQL you will find all of your rows in a time of 7 units at most. Whereas if you go with PHP and loop through all 100 rows, you will find your results in a time of 100 units. Now lets say you have 1,000,000 rows - with SQL it would take ln(1,000,000)/ln(2) = 20 units of time and with a flat file 1,000,000 units of time. Makes sense?

..you can look up search algorithms times online if you wish. The O( ln(n)/ln(2) ) was the fastest search algorithm based on sorted data I believe.

 

Thanks to everyone who replied, you all got rep .


Special thanks to motheninja, you made everything so clear. I have decided to go with the MySQL route.

 

If you are providing the option to search for some text you could implement word indexing.

If you do the regular

SELECT blabla FROM table WHERE column LIKE '%search term%'

Code (markup):

large parts of the table will be scanned.

MySQL 5+ (and all large commercial databases) also provide full text search options.

I don't know enough about your search needs to say that you can use it or benefit from it, but if you are searching for some text then I would definitely give it a try.