Excuse me: I rewrote the join of mysql into two, but there was a problem with the where condition.

excuse me: I rewrote the join of mysql into two, but the where condition encountered a problem
for example:
sql = select * from order o join user u on u.user_id = o.user_id where u.type = "Wechat Registration"
because the user table has been moved to another database and can only be accessed through the API.
split into:
sql1 = select user_id from user where type = "Wechat Registration";

sql2 = select * from order where user_id in (user_id extracted by sql1) limit 20 offset 0
when there is more and more data in the user table, there are tens of thousands of user_id fetched by sql1, so in sql2, user_id in (tens of thousands) is too slow to query. Is there any way to optimize it?

Mysql
Apr.02,2021

thinks your business needs are very strange.
this kind of related query should not be dismantled if you want to jointly check it. If you tear it apart, you don't need this kind of joint check.
and this kind of order registered by Wechat is directly redundant one order or user type in the order.
this kind of cross-instance join will not perform well in any way. When middleware is introduced, computing and caching are put into the middleware layer. Very dangerous

there is a reason for the slowness here
sql1 = select user_id from user where type = "Wechat registration";

type = "Wechat registration"; whether it can be optimized here, Chinese comparison.

The

interface returns data.

what do you do now that user has moved the orer foreign key? Whether user_id should have an index

several solutions

  1. introducing middleware can solve the join problem
  2. introduce redundant temporary user table to solve join
  3. whether a subquery of tens of thousands of data can be processed by business code. Do not run the join table

since there is a relationship, you forcibly tear it down into two libraries, drunk.
of course you can say you can't make up your mind, which is decided by a pat on the head at the top.
one solution is to regularly redundant a UserID table to your current database to facilitate join table queries, or use third-party search software to integrate data from various databases to speed up search, such as solr , sphinx .

Previous: How to fold the chart in the front table table?

Next: How does selenium click on the jump page after clicking on the jump?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b37e00-34496.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b37e00-34496.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?