SELECT statement, Multiple WHERE, with requirements

[BuildMyWeb]

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?

 

[QuickOldCar]

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

[mac_gyver]

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.

[Barand]

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?

[BuildMyWeb]

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!