Performance : SQL Queries vs Server-Side PHP Loops (MYSQL vs PHP)

Question

I have not seen much info on this topic and require some data . I am new to using complex MySQL queries and i was wondering about the performance costs of Complex queries in comparison to a simple query and then run server side php calculations on it .

E.g :

'Simple' query :

SELECT id as ID, date(x_date),  v_price as price
                From tableX
                WHERE v_price IN 
                (
                    select MIN(v_price) from tableX
                    GROUP BY week(x_date)
                )
            )

Now lets say i want to count the number of times similar prices occurred .

I can do it in 2 ways .

A) Use a foreach loop server side php and calculate the data

B) Do another select in sql

group by price
ORDER BY COUNT(price) DESC

Which is the best way to proceed ? via SQL or server code (php)

What are the performance costs , time , server load etc.....

sql, written efficiently, will almost always be faster

Rogue
– Rogue

2017-07-04 12:24:25 +00:00
Commented Jul 4, 2017 at 12:24 — Rogue
– Rogue, Commented Jul 4, 2017 at 12:24

Gordon Linoff · Accepted Answer · 2017-07-04 12:27:14Z

3

First, doing the work in the database is almost always better. This is discussed many times for each context where someone wants to do RBAR (row-by-agonizing row) processing.

Second, it is highly unlikely that this "simple" query does what you want:

SELECT id as ID, date(x_date), v_price as price
From tableX
WHERE v_price IN (select MIN(v_price) from tableX FROM tableX GROUP BY week(x_date))

This returns all rows in tableX where the minimum price matches any week's minimum price. More likely, you want:

SELECT id as ID, date(x_date), v_price as price
From tableX
WHERE (yearweek(x_date), v_price) IN (
          select yearweek(x_date), MIN(v_price)
          from tableX 
          group by yearweek(x_date)
         );

Note that this also takes the year into account.

answered Jul 4, 2017 at 12:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marek Vitek · Accepted Answer · 2017-07-04 12:39:14Z

1

Let' consider following example

select MIN(v_price) from tableX

What this will do is it will look up tableX by an index and find lowest price.
Now let's try to do it outside of DB server. First you have to get data from DB

select v_price from tableX

I hope you are seeing the difference already. It means pulling lot of data from storage on DB side and lot of data transfer. Even if your application and DB will be on the same machine it will mean lot of memory consumed and lot of CPU cycles spent on it.
So simple answer is do it in DB as it is almost always faster and less resource intensive.

answered Jul 4, 2017 at 12:39

Marek Vitek

1,65311 silver badges21 bronze badges

Collectives™ on Stack Overflow

Performance : SQL Queries vs Server-Side PHP Loops (MYSQL vs PHP)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related