Jump to content

SELECT statement, Multiple WHERE, with requirements


BuildMyWeb
Go to solution Solved by Barand,

Recommended Posts

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?

 

Link to comment
Share on other sites

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 by QuickOldCar
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.