Which way will have better performance - Filter/Order results with sql query or php?

Recently I tried new way to filter/order SQL queries: instead filter / order the results in SQL query I pull all data I need "as is" then doing the filters / orders with php code.

For example : I want only events with name like "test" order by Date .

Table struct :

id     eventDate     eventName

New way :

$query = mysqli_query($GLOBALS["link"], "SELECT `eventDate`, `eventName` FROM `tablename` WHERE `id`='X' ");
        while($data = mysqli_fetch_array($query))array_push($this->events,$data); 

Then I'm using array_fillter and usort /array_multisort and array_values in php...

Old way :

$query = mysqli_query($GLOBALS["link"], "SELECT `eventDate`, `eventName` FROM `tablename` WHERE `id`='X' AND (`eventName` LIKE '%test%') ORDER by `eventDate` DESC ") ;
        while($data = mysqli_fetch_array($query))array_push($this->events,$data); 

So what is better ? complex sql queries or pull all the data "as is" then make the filters and orders in php ?

The above example is very simple. I'm talking about much more complex filtering...

Please answer if you are absolutely sure !

Thanks :)

Answers


The answer will depend on a number of factors, including:

  1. The size of your database
  2. How the database is indexed
  3. The size of the result row
Pros for filtering with PHP:
  • Less Complex SQL Queries
  • Potentially Simpler Code
Cons for filtering with PHP:
  • Higher RAM usage, and with a large dataset, this can be a real deal breaker
  • Slower (Unless you have a table which is not indexed properly)
Pros for filtering with SQL:
  • Typically much faster, especially on properly indexed tables
  • Less RAM Usage
  • Less Data to Parse
Cons for filtering with SQL:
  • SQL queries can become unreadable if taken too far
  • Moving more logic to the query can make database interpretability more challenging (mySQL, SQLite, etc.)

Your milage my vary. Everyone has their own opinions, but in my personal experience, I've found that using native SQL filtering is typically the better choice. Remember the ultimate goal should be clean, maintainable code. And using an SQL Formatter goes a long way in making SQL more readable.


Logicaly mysql will be faster in all cases (avoid data transfer to php, mysql is written on c++), but it is the subject to test.

True power reveals with usage of indexes. In your case you use "like '%value%'" which disables index. But in easily solvable with fulltext index. With MySql 5.6 Fulltext indexes supported in InnoDB engine as well.


Need Your Help

iOS: Autolayout causing UIScrollView to not scroll

ios uiscrollview constraints autolayout

I have set up a UIScrollView with which I want to display 12 images (only 8 fit on screen) laid out horizontally. In the following image you can see the problem I'm having (which makes my scroll vi...

Making a DIV expand with content as opposed to scroll

html css html5 css3 overflow

Hey I am developing a website and I have hit a problem early on I added overflow:auto; to all of my main classes .header .content .footer because I was having a few background and ribbon errors whi...