0

I'm using the following SQL to fetch posts for each user by the user_id.

(select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 10) 
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 20)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 30)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 40)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 0)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 0)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 0)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 10)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 0)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 0)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 10)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 0)
union all (select * from `posts` where `posts`.`user_id` in (?) limit 10 offset 0)

The limit ? offset ? parts are used for the pagination for each user's posts.

For example limit 10 offset 20 means get "10 posts per page and page 3" for the specific user.

My Question is do I have better solution for this instead of union all to improve the querying performance? Obviously this SQL have too many sub queries…

plus, here is the context and requirements for my use case.

  • One query get both the user and the user's posts.
  • Each user can have multiple posts.
  • Each of the ? is a different user_id.
  • The limit ? offset ? can vary, but most of the time, the same value for each user.
yaquawa
  • 6,690
  • 8
  • 35
  • 48
  • Can you give us some context regarding how you are executing these queries? Is it all at once, or one at a time? Also, what would be filling those `?` placeholders, and would it also be the same value(s)? – Tim Biegeleisen Oct 05 '18 at 05:01
  • 4
    Why do you need to get different pages for different users at the same time? – Barmar Oct 05 '18 at 05:38
  • 4
    Using LIMIT and OFFSET without ORDER BY doesn't make much sense. – Barmar Oct 05 '18 at 05:39
  • First you should avoid using `SELECT *` and get only relevant columns (as required by your application code), by specifying their names. Read: [Why is SELECT * considered harmful?](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) – Madhur Bhaiya Oct 05 '18 at 06:28
  • @Tim Biegeleisen Thanks! I added some extra description for this question. – yaquawa Oct 05 '18 at 08:24
  • @Barmar Hi, can you give some example code? Thanks! – yaquawa Oct 05 '18 at 08:25
  • @Madhur Bhaiya Because this is a ORM implementation I have no other choices. – yaquawa Oct 05 '18 at 08:27
  • @yaquawa I am pretty sure that in an ORM, you can specify which fields to fetch – Madhur Bhaiya Oct 05 '18 at 08:51
  • 1
    @yaquawa If you're doing paging based on the dates of the posts, you should use something like `ORDER BY date_posted DESC` to get the newest posts first. Without `ORDER BY`, the criteria for paging is unpredictable and might not even be consistent from one query to the next. – Barmar Oct 05 '18 at 14:40
  • `OFFSET` is a performance killer by definition. If you need performance use index pagination with `ORDER BY` instead. – The Impaler Oct 05 '18 at 16:05
  • @The Impaler What do you mean by `index pagination`? – yaquawa Oct 06 '18 at 19:15
  • @yaquawa I meant "key set" pagination. The efficient way of paginating is by using a unique set of columns to order and paginate with, so you avoid using the *evil* `OFFSET`. – The Impaler Oct 06 '18 at 22:00
  • @The Impaler Never heard about that.. Do you mean something like this? https://stackoverflow.com/questions/21540670/mysql-select-list-after-a-specific-id – yaquawa Oct 08 '18 at 17:27

0 Answers0