CODEWITHSUNDEEP
mysql
aaa
aaa

Reputation: 546

MYSQL SELECT with LIMIT from multiple Databases and Order By

I have an application that uses multiple MYSQL Databases and result is merged into one, but there is an issue when using to my queries LIMIT.

I have:

MYSQL 1 - view_users

MYSQL 2 - view_users

MYSQL 3 - view_users

Have query:

SELECT * FROM `view_users` 
ORDER BY `date` DESC LIMIT 0, 25

The result that i get is correct:

MYSQL 1 - return 25 rows

MYSQL 2 - return 25 rows

MYSQL 3 - return 25 rows

The result that i want to achieve is to get just 25 rows instead of 75 rows, because my pagination broke.

Desired result: MYSQL 1, MYSQL 2, MYSQL 3 - return 25 rows in total

Is that possible to achieve this on MYSQL level?

Upvotes: 1

Views: 303

Answers (2)

Ryoma
Ryoma

Reputation: 64

As I know, Mysql don't support your requirement.

But have two solutions for this:

  • 1: multi Mysql databases store data in order: so you can get you wanted sorted data.
  • 2: if data stored out-of-order: you get each 25 rows from three database, then you must rank 25 * 3 = 75 rows data and get returned 25 rows data. But you need to notice, If you want get 25-50 rows data, you also must get 50 rows data from three database and rank, it's very complex.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

if the different db are on the same server you could use union 

  SELECT * 
  FROM db1.`view_users` 
  UNION 
  SELECT * 
  FROM db2.`view_users` 
  UNION 
  SELECT * 
  FROM db2.`view_users` 
  ORDER BY `date` DESC LIMIT 0, 25

Upvotes: 2

Related Questions