我想提高我的搜索性能

axr492tv 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(337)

我想提高我的搜索性能。我想根据服务和专业搜索用户。用户按专业或服务应用的筛选器列出，也可以两者都列出。我要从表结构下的所有表中获取数据，共有5个表

1) users            :- id ,first_name,last_name 
2) users_services   :- id ,user_id,service_id
3) users_speciality :- id,user_id,specility_id
4) mst_services     :- id,name
5) mst_speciality   :- id,name

我已经使用这个查询来得到结果，它工作得很好。

select  u.id,first_name,last_name,location,services.name as service_name,speciality.name as specility_name from users as u 

inner join  users_services  on u.id =users_services.user_id

Inner join mst_services as services on  services.id=users_services.service_id

inner join  users_speciality  on u.id =users_speciality.user_id
Inner join mst_speciality as speciality on  speciality.id=users_speciality.service_id WHERE speciality.name ="specificity one"

从规范化的Angular 来看似乎是正确的，但是当数据超过100000时，加入太多表的时间可能会导致问题。我应该如何根据服务和专业筛选用户？

mysql

来源：https://stackoverflow.com/questions/51479335/i-want-to-improve-my-search-performance-mysql

1条答案

按热度按时间

k4ymrczo1#

你试过这样吗？

SELECT users.id,first_name,last_name, mst_services.name as service, mst_speciality.name AS speciality
FROM users 
LEFT JOIN users_services ON users.id = users_services.user_id
LEFT JOIN users_speciality ON users.id = users_speciality.user_id
LEFT JOIN mst_services ON service_id = mst_services.id
LEFT JOIN mst_speciality ON speciality_id = mst_speciality.id
WHERE users.id IN
(SELECT user_id FROM users_services
WHERE service_id = (SELECT id FROM mst_services WHERE name = "service one")
UNION ALL
SELECT user_id FROM users_speciality
WHERE speciality_id = (SELECT id FROM mst_speciality WHERE name = "spercial one"))
ORDER BY first_name,last_name,service,speciality

赞(0）回复(0）举报 2021-06-21

我来回答

我想提高我的搜索性能

1条答案

相关问题

热门标签

最新问答