CrustyDOD Posted May 27, 2008 Share Posted May 27, 2008 Hello! I have a slight problem that i just figured out. Since i'm naming fields in my own language, i'll just use a general example. Same structure tho. table 1: CD info (fields) - id - cd_title - date_added table 2: Music Genres (fields) - id - genre_name table 3: CD Genres (fields) - id - cd_id (ID of CD record) - genre_id (ID of genre record) Table 1 contains list of CD's and each CD has 1 or more genres. Is it possible to get genre_name for each record with 1, 2 or 3 queries in total? Basically table 3 is just a link between table 1 and table 2. Right now, i first get let's say 50 CD's from table 1 and then inside a loop i do another query that get's CD Genres. NOT ACTUAL CODE.. But it shows the current way "Select * from table1 limit 50" LOOP ( "select * from table3 left join table 2 on table2.id = table3.genre_id where table3.cd_id = 1" ) This is just bad! I get 51 queries per page load. Quote Link to comment https://forums.phpfreaks.com/topic/107459-solved-join-or-something-else-to-get-multi-records-from-second-table/ Share on other sites More sharing options...
Barand Posted May 27, 2008 Share Posted May 27, 2008 you only need one query SELECT cd.cd_title, GROUP_CONCAT(g.genre_name) AS genres FROM table1 AS cd JOIN table3 AS cdg ON cd.id = cdg.cd_id JOIN table2 AS g ON cdg.genre_id = g.id GROUP BY cd.cd_title Quote Link to comment https://forums.phpfreaks.com/topic/107459-solved-join-or-something-else-to-get-multi-records-from-second-table/#findComment-550961 Share on other sites More sharing options...
CrustyDOD Posted May 27, 2008 Author Share Posted May 27, 2008 That is exactly what i want! 1 problem tho. 5 total, Query took 200.1998 sec Limit was set to 5, tables contain (total records): CD table: 129,997 genre table: 21 CD_Genres: 324,516 Index keys are set to fields that i'm looking... Any ideas? Oh and i tried this on mysql 5 version Quote Link to comment https://forums.phpfreaks.com/topic/107459-solved-join-or-something-else-to-get-multi-records-from-second-table/#findComment-551021 Share on other sites More sharing options...
Barand Posted May 27, 2008 Share Posted May 27, 2008 I assume that the ids are the primary keys on table1 and table2. Do you have indexes on cd_id and genre_id on table3? Quote Link to comment https://forums.phpfreaks.com/topic/107459-solved-join-or-something-else-to-get-multi-records-from-second-table/#findComment-551022 Share on other sites More sharing options...
CrustyDOD Posted May 27, 2008 Author Share Posted May 27, 2008 Yeah. Running optimize solved it Takes half a second now. Quote Link to comment https://forums.phpfreaks.com/topic/107459-solved-join-or-something-else-to-get-multi-records-from-second-table/#findComment-551051 Share on other sites More sharing options...
Barand Posted May 27, 2008 Share Posted May 27, 2008 Quite an improvement! Quote Link to comment https://forums.phpfreaks.com/topic/107459-solved-join-or-something-else-to-get-multi-records-from-second-table/#findComment-551053 Share on other sites More sharing options...
CrustyDOD Posted May 27, 2008 Author Share Posted May 27, 2008 Yeah, its actually my bad. Before going live i wanted to see how queries will hold againts large amount of data, so i inserted fake data to tables and forgot to optimize. Shit happens i guess. Thanks for the GROUP_CONCAT solution! Quote Link to comment https://forums.phpfreaks.com/topic/107459-solved-join-or-something-else-to-get-multi-records-from-second-table/#findComment-551096 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.