How performance is affected by storing categories as comma delimited array and extracting each in a loop?

Question

Namastey!

I'm currently working on one of my projects where I get to one doubt. What I'm doing is:

I have got 4 tables called:

1: project_degree
2: project_department
3: project_category
4: Projects

project_degree have got four top level degrees which are: B.tech, M.tech, M.C.A. & M.B.A.

project_department have got their respective departments such as: C.S.E, I.T., E.E.E. and so on.

Now, project_category have some categories like PHP, .NET, SAP etc.

What I'm trying to do is, I'm adding a project to database called "projects" where I'm assigning it to its degree, department and category.

Structure of projects table is as follows:

id    |    project name    |    degree_id    |    Dept_id    |    Cat_id

1     |   Sample project   |        1        |       3       |   4,3,6,5,9

Now as you can see above, field "cat_id" have got multiple categories where each categories ID have been separated by a comma.

Now when I call a query get all projects from the table called "projects", I want to list of categories related to that particular project and my code goes like this:

$query = mysql_query("SELECT * from projects ORDER BY id DESC");
while($row = mysql_fetch_array($query)) {
    $categories = $row['cat_id'];
    $cat_arr    = explode(',',$categories);
    $cat_size   = sizeof($cat_arr);

    for($i=0;$i<$cat_size;$i++) {
        $get_cat = mysql_query("SELECT * from project_category WHERE id='".$cat_arr['$i']."'");
        $cat_row = mysql_fetch_array($get_cat);
        echo $cat_row['name']; // Here finally I'm getting categories name
    }
}

So, finally I'm running nested loop and two queries to get a category name. I doubt it affect the page load time when it comes to huge data. Is there any better alternative to this situation?

Performance is *a* concern, but a *sane, easy-to-query* data structure is the bigger concern. *Normalize* your database! Google "database normalization". — deceze, Aug 06 '13 at 09:06
Normalizing the database will improve performance, because you can then use a JOIN statement in your SQL; and switching from MySQL to MySQLi or PDO so that you can use prepared statements and bind variables will allow you to improve security - I'd hope that any site with data about degree courses would do both and practise what they should be preaching — Mark Baker, Aug 06 '13 at 09:11
I think the easiest way to improve the performance will be to create a link table between the categories and projects i.e. Normalization. Still you can refer this question for some options. http://stackoverflow.com/questions/6861258/how-to-join-two-tables-using-a-comma-separated-list-in-the-join-field — Saurabh, Aug 06 '13 at 09:16

score 0 · Answer 1 · answered Aug 06 '13 at 09:09

0

Here should be work

$query = mysql_query("SELECT * from projects ORDER BY id DESC");
while($row = mysql_fetch_array($query)) {
    $get_cat = mysql_query("SELECT * from project_category WHERE id IN (" . $row['cat_id'] . ")");
    $cat_row = mysql_fetch_array($get_cat);
    echo $cat_row['name']; // Here finally I'm getting categories name
}

But normalization is better.

answered Aug 06 '13 at 09:09

Allen Chak

1,802
1
10
21

Nested loops with multiple select queries is not a good solution: Normalization, use of a join, and a single select is a far better alternative – Mark Baker Aug 06 '13 at 09:13
This answer is simply wrong. Inside the while loop, you execute a query that returns multiple categories and only get the name of (probably) the first one. – mlkammer Aug 09 '13 at 14:34

score 0 · Answer 2 · answered Aug 06 '13 at 09:15

It's better to add a new table, project_has_catID where you can store project.id and cat.id as two columns (project_id and cat_id). Remove the Cat_id column in the project table. Once done, simply select the name by the next query;

$query = mysql_query("SELECT project_category.name from project_category INNER JOIN project_has_catID phc ON phc.cat_id = project_category.id ORDER BY phc.project_id DESC");

By this, you have a list of category names. You don't have to loop with multiple database communications.

You're right. But this does require changes in database structure. See my answer - it doesn't require these changes. — mlkammer, Aug 09 '13 at 14:37

score 0 · Answer 3 · answered Aug 06 '13 at 09:21

You could load all category names into an associative array first, then look up category names in this array when looping through the projects.

$categoryNames = array();
$query = mysql_query("SELECT id, name FROM project_category");
while ($row = mysql_fetch_array($query))
{
    $categoryNames[$row['id']] = $row['name'];
}
$query = mysql_query("SELECT * FROM projects ORDER BY id DESC");
while ($row = mysql_fetch_array($query))
{
    $categoryIds = explode(',', $row['cat_id']);
    foreach ($categoryIds as $cat_id)
    {
        $cat_name = $categoryNames[$cat_id];
        //do what you want with the name here
    }
}

This way, you don't have to change your database structure, or the structure of your code. And you only have to execute two queries in total, so this is by far the simplest solution.

Needless to say, normalization is always better as others have indicated (never use a column that contains multiple comma-separated values), but you probably knew that and had your reasons for setting up your database tables this way.

How performance is affected by storing categories as comma delimited array and extracting each in a loop?

3 Answers3