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. 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 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 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? 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. 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! 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! 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
Archived
This topic is now archived and is closed to further replies.