mysql - 如何高效的查询需要合并大数据表的操作

Question

1.存在两个数据库Shanghai和Beijing（假设其他数据丢失，中国人口数据只剩下上海和北京）
2.两个数据库中都有同样结构的表table_people，数据量都在一千万左右
3.有以下数据
Shanghai.table_people

id	username	birthday
1	jhon	2016-08-15 12:00:00
2	may	2016-08-14 13:00:00
3	abcd1111	2015-08-01 14:00:00
...	....	.....

Beijing.table_people

id	username	birthday
1	mike	2016-08-16 23:58:00
2	kitty	2016-08-03 15:00:06
3	ab111	2014-01-01 15:16:18
...	....	.....

问：
1.如何高效的(1s内)查出最近在中国出生的前十个人
2.对于YII框架数据提供器应该怎么写

Answer 1

(SELECT * FROM Beijing.table_people ORDER BY birthday limit 10)
union all
(SELECT * FROM shanghai.table_people ORDER BY birthday limit 10)
ORDER BY birthday limit 10

Do you think this idea is okay?

Answer 2

The birthday type must be datetime, not string, and then be indexed. When checking, first union all, then order by, then limit 10. Mysql should internally do a merge sort on the two indexes

If you want it to be faster, change the birthday type to int

Answer 3

Each table fetches the first 10 pieces of data that meet the conditions, then sorts the retrieved data in the code and retrieves the final top 10 pieces of data.

However, writing code in this way is also very tiring. In the future, if you add a table, you will have to change the code, so it is recommended to look for relevant information about database middleware.

Answer 4

Two sql, according to id desc and limit 10, are inserted into a temporary table, sort the 20 pieces of data by time and then limit 10