!= in sql or != in php

Question

Task: get display 10 objects except 1 specific Solutions:

get 11 objects from DB, and do something like this

foreach ($products as $product) { if($product->getId() != $specificProduct->getId()){ //display } }
just add condition in sql query WHERE p.id != :specific_product_id

Some additional information: we use doctrine2 with mysql, so we must expect some additional time by hydration. I have made some test, I timed both of this solutions but I still haven't any idea which way is better.

So, I have gotten some strange results by my test(get 100 queries with different parameters)

php = 0.19614
dql = 0.16745
php = 0.13542
dql = 0.15531

Maybe someone have advice about how I should have made my test better

do as much as you can in the SQL... it makes life a lot easier... — gloomy.penguin
– gloomy.penguin, Commented Oct 18, 2013 at 22:56
@tereško, almost every SQL implementations supports both forms of "not equal" -- see Should I use != or <> for not equal in TSQL? — Bill Karwin
– Bill Karwin, Commented Oct 18, 2013 at 23:03

Bill Karwin · Accepted Answer · 2013-10-18 22:59:08Z

If you're concerned about the overhead of hydration, keep in mind that doing the != condition in PHP code means you have to fetch thousands of irrelevant, non-matching rows from the database, hydrate them, and then discard them.

Just the wasted network bandwidth for fetching all those irrelevant rows is costly -- even more so if you do that query hundreds of times per second as many applications do.

It is generally far better to eliminate the unwanted rows using SQL expressions, supported by indexes on the referenced tables.

Andersnk · Accepted Answer · 2013-10-18 23:02:25Z

Use SQL as much as possible. THis is the basic query, you could use. This is much more effecient than discarding the rows in PHP.

$query = "SELECT * FROM table_name WHERE p.id <> '$specific_id'"

tanaydin · Accepted Answer · 2013-10-18 23:02:54Z

I think such as queries (like id based, or well indexed) must be on sql side... cause it uses indexes, and returns less data to your application. processing less data makes your applications runs faster.

Collectives™ on Stack Overflow

!= in sql or != in php

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related