thara Posted June 7, 2015 Share Posted June 7, 2015 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 7, 2015 Share Posted June 7, 2015 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? Quote Link to comment Share on other sites More sharing options...
thara Posted June 7, 2015 Author Share Posted June 7, 2015 @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. Quote Link to comment Share on other sites More sharing options...
fastsol Posted June 7, 2015 Share Posted June 7, 2015 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 | +---------+-----------+ Quote Link to comment Share on other sites More sharing options...
Barand Posted June 7, 2015 Share Posted June 7, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
thara Posted June 7, 2015 Author Share Posted June 7, 2015 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. Quote Link to comment Share on other sites More sharing options...
fastsol Posted June 7, 2015 Share Posted June 7, 2015 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 7, 2015 Share Posted June 7, 2015 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 | +------------+---------------+-----------+ Quote Link to comment Share on other sites More sharing options...
Barand Posted June 8, 2015 Share Posted June 8, 2015 (edited) 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 1 Quote Link to comment Share on other sites More sharing options...
CroNiX Posted June 8, 2015 Share Posted June 8, 2015 find_in_set won't use indexes, so yes it will get a LOT slower as the tables grow. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.