give the job to MySQL or to PHP?

Question

I have some several codes in PHP to do jobs that MySQL could do.

such as sorting, merging each data from different MySQL tables, etc...

Lately, I found out that I can do all these stuffs with one MySQL query.

I am wondering is it better to give the MySQL capable jobs to MySQL or to PHP. efficiencies, speed, etc..

Thank You,

Answer 1

Your performance will increase if you let MySQL handle that work.

Answer 2

DO it in MySQL. There's no question that is more efficient. PHP will use much more memory, for one.

Answer 3

It will be better performing to do this in MySQL. Firstly, it has optimized sorting algorithms for the data and can utilize indexes which are created. Furthermore, if it is merging and filtering, you will end up transfering less data from the database.

Answer 4

Hi,

Databases are optimized to carry out these functions while retrieving the data. Sorting at database level is much more easier to read than writing tens of line for coding in PHP over the lists or collection

There are ready String functions available in MySQL to merge the data while retrieving the data from the database.

I definitely would suggest MySQL.

Kalpak Luniya

Answer 5

If you do it in PHP you are just re-implementing the features that MySQL already has. It's far from the most optimized solution and therefore it is much slower.

You should definately do it in the SQL query.

Answer 6

No question: MySQL is built for this. To add something, maybe you'd be intrested in building joint table queries (multiple table queries). It is very helpful and really very simple. For instance:

$query = "SELECT DISTINCT post.title as title, post.id as id, 
                    product.imageURL as imageURL, product.dueDate as dueDate 
                    FROM post, product 
                    WHERE post.status='saved'  
                    AND post.productURL=product.linkURL 
                    AND post.userEmail='$session[userEmail]' 
                    AND NOT EXISTS(
                        SELECT publication.postId FROM publication 
                        WHERE publication.postId=post.id
                        ) 
                    ORDER BY post.id";

This is a simple example from some code i built. The thing is it merges 2 different tables with the restriction of post.productURL=product.linkURL. It also uses negation, pretty useful when the set you are looking for is not defined by any condition but instead the absence of one. You can avoid this by building views in MySQL as well.

I'm a newbie myself, so I hope it helps. Cheers.