How I make this kind of Select in MySql?

[thara]

I have a table and it looks like this,

    mysql> select * from match_info;
    +---------------+---------+------------+-------------+-----------+
    | match_info_id | user_id | body_types | hair_colors | ethnicity |
    +---------------+---------+------------+-------------+-----------+
    |             1 |       1 | 1,5,9,13   | 1,5,9,13    | 2,6,10    |
    |             2 |       2 | 1,5,9,13   | 5           | 1,5,9     |
    +---------------+---------+------------+-------------+-----------+

I have used 3 tables for `body_types`, `hair_colors` and `ethnicity` with `id` and `name` columns in each table.

Using above I need to select all values for particular user. Like this.

Body Type: `Skinny, Muscular, Large, Ripped`

Hair Color: `Blonde, Dark Brown, Strawberry Blonde, Dark Blonde` etc....

Can anybody tell me how I make a select query to get result as above. And also  I would like to know is this database design is good or not?

Hope somebody may help me out.

Thank you.

[Barand]

If you store the data as comma delimited strings there is no way you can join those ids to the respective tables.

 

Do you have users with three different ethnicities and four different body types?

[thara]

@Barand, I am using checkboxes to select their body types, hair colors and ethnicity. Practically users can not have more ethnicity but body type and hair colors may have more than one. On this situation they can select more than one option using those checkboxes.  Thats why I use comma separated string in "match_info" table.

[fastsol]

Ok so then this table is actually not needed and will only make life harder in the future.  What you need is 2 reference tables to go between the users table and the hair color and body type tables.  Since ethnicity should really only have one value to a user, there shouldn't be any reason to store more than a single value per user.  So the reference tables have only 2 columns, the user id and the id or the hair color or id of the body type. Then you use JOIN to gather the info from all the tables by referencing the ids between them.

table name -> body_types_reference 
    +---------+-----------+
    | user_id | body_types|
    +---------+-----------+
    |       1 |         1 |
    |       2 |         1 |
    |       2 |         5 |
    |       2 |         9 |
    |       1 |         5 |
    +---------+-----------+

[Barand]

As I said, if you store in comma delimited strings you cannot use JOINS to the data you need. If you do it correctly as fastsol has shown you then

SELECT user_id
  , GROUP_CONCAT(bt_name SEPARATOR ', ') as bodytypes
  , GROUP_CONCAT(hc_name SEPARATOR ', ') as haircolours
  , e.name as ethnicity
FROM usertable u
    INNER JOIN body_type_reference btr ON u.user_id = btr.user_id
    INNER JOIN body_type bt ON btr.body_type = bt.id
    INNER JOIN hair_colour_reference hcr ON u.user_id = hcr.user_id
    INNER JOIN hair_colour hc ON hcr.hair_colour = hc.id
    INNER JOIN ethnicity e ON u.ethnicity = e.id

Edited June 7, 2015 by Barand

[thara]

Thank you... I understood and I do need to change my table structure. But with my existing table structure I got a solution with mysql FIND_IN_SET() string function and this is how my SELECT query look like. 

select t.match_info_id, t.user_id,
    group_concat(distinct a.name) as body_types,
    group_concat(distinct b.name) as hair_colors,
    group_concat(distinct c.name) as ethnicity
from match_info as t
    inner join body_type as a on find_in_set(a.id,t.body_types)
    inner join hair_color as b on find_in_set(b.id,t.hair_colors)
    inner join ethnicity as c on find_in_set(c.id,t.ethnicity)
group by t.match_info_id, t.user_id

This is the output from above query:

+---------------+---------+-------------------------------------+---------------------------+---------------------------------------------+
| match_info_id | user_id | body_types                          | hair_colors               | ethnicity                                   |
+---------------+---------+-------------------------------------+---------------------------+---------------------------------------------+
|             1 |       1 | Skinny,Large                        | Silver/White,Partial Gray | Asian                                       |
|             2 |       2 | Skinny,Husky,Few extra pounds,Large | Silver/White              | White/Caucasian,Mixed Race,Pacific Islander |
+---------------+---------+-------------------------------------+---------------------------+---------------------------------------------+
2 rows in set (1.87 sec)

Any idea would be greatly appreciated regarding this query... 

 

Thank you.

[fastsol]

If it takes 1.87 seconds to complete a 2 row result, that's terrible.  Just think how long that would take with 100 or 1000 rows.

[Barand]

I created a table with 1000 users to benchmark the two methods, viz.

-- 
-- METHOD 1
-- 
SELECT username
, GROUP_CONCAT(DISTINCT hc.name) as haircolours
, GROUP_CONCAT(DISTINCT e.name) as ethnicities
, GROUP_CONCAT(DISTINCT bt.name) as bodytypes
FROM users u
INNER JOIN body_type bt ON FIND_IN_SET(bt.id,u.body_types)
INNER JOIN hair_color hc ON FIND_IN_SET(hc.id,u.hair_colors)
INNER JOIN ethnicity e ON FIND_IN_SET(e.id,u.ethnicity)
GROUP BY username;

-- 
-- METHOD 2
-- 
SELECT username
  , GROUP_CONCAT(DISTINCT hc.name) as haircolours
  , GROUP_CONCAT(DISTINCT e.name) as ethnicity
  , GROUP_CONCAT(DISTINCT bt.name) as bodytypes
FROM users u
    INNER JOIN user_body btr ON u.id = btr.user_id
    INNER JOIN body_type bt ON btr.body_id = bt.id
    INNER JOIN user_hair hcr ON u.id = hcr.user_id
    INNER JOIN hair_color hc ON hcr.hair_id = hc.id
    INNER JOIN user_ethnic et ON u.id = et.user_id
    INNER JOIN ethnicity e ON et.ethnic_id = e.id
GROUP BY username;

The first time I ran it, with 3 items in each delimited column, I was surprised that the FIND_IN_SET was faster

+------------+---------------+-----------+
| METHOD 1   |  FIND_IN_SET  |    0.062s |
| METHOD 2   |  JOINS        |    0.125s |
+------------+---------------+-----------+

The next time time I increased to 10 items in each list and the FIND_IN_SET degraded significantly

+------------+---------------+-----------+
| METHOD 1   |  FIND_IN_SET  |    3.510s |
| METHOD 2   |  JOINS        |    0.109s |
+------------+---------------+-----------+

[Barand]

Decided to run the tests again, varying the number of items in the sets (N) from 2 to 10 so I could see how it varied with increases.

 

Method:

• Re-define N to the number required in each set
• Run the script to generate the data (code below)
• Run queries for both methods and take the times (MySql Workbench)

Data generation code

<?php
include ('db_inc.php');  // DB CREDENTIAL DEFINITIONS
$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

define("N", 2);                  // How many values in each set

$db->query("DELETE FROM users");
$db->query("DELETE FROM user_hair");
$db->query("DELETE FROM user_body");
$db->query("DELETE FROM user_ethnic");

$values = range(1,15);
updateTables($db, $values, 1, 500);         // first 500 users
updateTables($db, $values, 501, 1000);      // second 500 users
echo "Finished";

function updateTables($db, $values, $ufrom, $uto)
    /****
    * Function to generate the comma delimited sets
    * and write records to the link files 
    */
{
    for ($u=$ufrom; $u<=$uto; $u++) {
        shuffle($values);
        $hair = array_slice($values,0,N);   // get N random values
        $body = array_slice($values,1,N);
        $ethnic = array_slice($values,2,4);
        $username = "User $u";
        $uhvals = join(',', $hair);            // create set of N items
        $ubvals = join(',', $body);
        $uevals = join(',', $ethnic);
        $sex = $u%2 + 1;
        $udata[] = "($u,'$username',$sex,'$uhvals','$ubvals','$uevals')";

        foreach ($hair as $h) {        // add N records to link tables
            $hdata[] = "($u,$h)";
        }
        foreach ($ethnic as $e) {
            $edata[] = "($u,$e)";
        }
        foreach ($body as $b) {
            $bdata[] = "($u,$b)";
        }
    }

    $sql = "INSERT INTO users VALUES ".join(',',$udata);
    $db->query($sql);
    $sql = "INSERT INTO user_body VALUES ".join(',',$bdata);
    $db->query($sql);
    $sql = "INSERT INTO user_hair VALUES ".join(',',$hdata);
    $db->query($sql);
    $sql = "INSERT INTO user_ethnic VALUES ".join(',',$edata);
    $db->query($sql);
}
?>

The tables (15 types in each of the body_type, hair_color and ethnicity tables so I could select 2 -10 at random)

 

 

CREATE TABLE `users` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT 'male',
  `hair_colors` varchar(45) DEFAULT NULL,
  `body_types` varchar(45) DEFAULT NULL,
  `ethnicity` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `user_body` (
  `user_id` int(11) NOT NULL,
  `body_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`body_id`),
  KEY `idx1` (`body_id`)
);

CREATE TABLE `user_hair` (
  `user_id` int(11) NOT NULL,
  `hair_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`hair_id`),
  KEY `idx1` (`hair_id`)
);

CREATE TABLE `user_ethnic` (
  `user_id` int(11) NOT NULL,
  `ethnic_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`ethnic_id`),
  KEY `idx1` (`ethnic_id`)
);

CREATE TABLE `body_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `hair_color` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `ethnicity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

 

 

 

Results attached

Edited June 8, 2015 by Barand

[CroNiX]

find_in_set won't use indexes, so yes it will get a LOT slower as the tables grow.