CODEWITHSUNDEEP
phpmysqlfor-loopforeachinner-join
user3530012
user3530012

Reputation: 740

Inner join vs foreach loop performance

I'm trying to do something and I'm sure if I'm doing it in a right way. Let's say I have two tables in MySQL database with the following columns

-------------------------

Members
-------------------------
id        name      status
-----     -----     -----
1         Mike      0
2         Alex      0
3         John      1
4         Jack     -1

-------------------------

    Status
    -------------------------
    id        text
    -----     -----
   -1        disapproved
    0         reviewing
    1         approved

Now I need to display the information of the table "Members" in the following way:

id        name      status
-----     -----     -----
1         Mike      reviewing
2         Alex      reviewing
3         John      approved
4         Jack      disapproved

One way is to use an Inner Join between the two tables and the other way is to extract data from the "Members" table and put it inside a for-each loop and convert status values. In the latter way I don't need the "status" table at all.

I'm not sure which way is better. please let me know if you need more information to help me.

Thanks in advance.

Update

An for-each example for what I want to do is like this, supposing I have extracted data from "Memberes" table

foreach($data as $value)
    {
    if ($value['status'] == 0)
        $value['status'] = 'reviewing';
    if ($value['status'] == 1)
        $value['status'] = 'approved';      
    else
        $value['status'] = 'diapproved';        
    }

Upvotes: 3

Views: 2474

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

From a performance point of view, in most cases, there is almost no penalty for an inner join using a primary key relationship. MySQL has spent years on optimizing precisely this use case - it's called "relational database" because that's what it's designed to do.

In the example you give, the performance cost would be impossible to measure on decent hardware, even with tens of millions of users.

On the other hand - your code sample shows why this is a terrible idea.

You have a typo.

 else
        $value['status'] = 'diapproved';        
    }

I think you mean "disapproved" - that's what your database says.

Because you are repeating information, you're introducing the risk of bugs. A developer who has to fix the typo now has to look in two places to fix the bug - but which one is right? The database, or the PHP code?

And what happens when you introduce status 2 - needs validation? Now you have to update the code in two places (and watch out for those typos!).

There are cases where performance improvements are necessary. This almost certainly isn't one; if it is, try to solve it in the database by optimizing your queries.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

In general, you absolutely want to be doing this via a join inside your MySQL database:

SELECT
    t1.id, t1.name, t2.text AS status
FROM Members t1
INNER JOIN Status t2
    ON t1.status = t2.id

The alternative of handling this inside your PHP code with a foreach loop is unattractive for several reasons. First, you would probably need to bring in all information from both tables, which is wasteful from both a memory and network usage point of view. Then, even after you have brought in the data, you would be relying on PHP to perform the join. PHP wasn't really designed for in house database operations, and cannot use something like an index to speed up the process.

Upvotes: 6

Related Questions