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 !
The answer will depend on a number of factors, including:
- The size of your database
- How the database is indexed
- 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.