how to optimize if db and table use same sharding rule

[caorong]

in my case, db and table sharding rule are the same.

my dataNdoe: db_${0..3}.order_${0..11}

if I query only one user_id, it works well

mysql> PREVIEW SELECT * FROM order where user_id in (1)  order by order_id desc limit 11;
+------------------+------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                         |
+------------------+------------------------------------------------------------------------------------+
| db_1          | SELECT * FROM order_1 where user_id in (1)  order by order_id desc limit 11 |
+------------------+------------------------------------------------------------------------------------+

mysql> PREVIEW SELECT * FROM order where user_id in (2)  order by order_id desc limit 11;
+------------------+------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                         |
+------------------+------------------------------------------------------------------------------------+
| db_2          | SELECT * FROM order_2 where user_id in (2)  order by order_id desc limit 11 |
+------------------+------------------------------------------------------------------------------------+

but if I query multi user_id, it will query meanless table which always empty.

mysql> PREVIEW SELECT * FROM order where user_id in (1,2)  order by order_id desc limit 11;
+------------------+------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                         |
+------------------+------------------------------------------------------------------------------------+
| db_1          | SELECT * FROM order_1 where user_id in (1,2)  order by order_id desc limit 11 |
| db_1          | SELECT * FROM order_2 where user_id in (1,2)  order by order_id desc limit 11 |
| db_2          | SELECT * FROM order_1 where user_id in (1,2)  order by order_id desc limit 11 |
| db_2          | SELECT * FROM order_2 where user_id in (1,2)  order by order_id desc limit 11 |
+------------------+------------------------------------------------------------------------------------+

because in current shardingsphere inner logic.

the result of sharding db and sharding table are isolated.

use hint query also can not resolve this.

How to optimize?

[strongduanmu]

Can you show your sharding rule configuration?