BuildMyWeb Posted August 8, 2015 Share Posted August 8, 2015 so i am developing an app where users submit content that is categorized by region. we then have a viewing page where 50 most recent submissions are displayed in a carousel for each region. so imagine if we have 5 regions, we thusly have 5 carousels, each with 50 most recent submissions. i imagine in the scenario above, i will need 5 separate SELECT statements that look for each particular REGION? any more efficient way to accomplish this other than 5 calls to the server? $query = "SELECT * FROM ( SELECT file_name, region, content, user_ip FROM table WHERE region=? ORDER BY id DESC LIMIT 50 ) sub ORDER BY id ASC"; secondarily, this presents an issue i contend with whenever working with sql projects. i imagine faster indexing with 5 smaller tables, one for each REGION, as opposed to one large table with a REGION column is the way to go. but i dont really have much experience to say anything with certainty. one table or 5? Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted August 12, 2015 Share Posted August 12, 2015 (edited) If you want them neatly categorized like you say, five queries is most likely the easiest way. I had some code to do multiple LIKE and even another doing REGEXP, you can query x many results, but that wouldn't guarantee are going to get 50 results each region. Using one table for this should be just as fast as trying to use five if is indexed properly. You may be thinking if need to pull specific region data it could be easier if each had it's own table. (still be five queries and now using JOIN when need all the data) If is just five regions I wouldn't worry about it, if was a lot of them I would do multiple tables. Edited August 12, 2015 by QuickOldCar 1 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted August 12, 2015 Share Posted August 12, 2015 any more efficient way to accomplish this other than 5 calls to the server? a UNION query built from the x number of SELECT queries would result in the fewest round-trip communications/queries ran. Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted August 12, 2015 Solution Share Posted August 12, 2015 Separate tables will be a PIA when you need to query for results across regions. EG How many submissions have we had in the last three months? Quote Link to comment Share on other sites More sharing options...
BuildMyWeb Posted August 15, 2015 Author Share Posted August 15, 2015 thank you guys. i had not received emails on this topic. thought no one replied i have found what Barand foresaw, querying results across regions is a PIA. back to one table! 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.