Jump to content

How I make this kind of Select in MySql?


thara

Recommended Posts

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.

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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 |
    +---------+-----------+
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 |
+------------+---------------+-----------+
Link to comment
Share on other sites

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

post-3105-0-24205200-1433756909_thumb.png

Edited by Barand
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.