在php mysql查询中我加入了8个表，结果是处理时间很长。 给我任何解决方案

Question

SELECT o.*, GROUP_CONCAT(DISTINCT ot.name) organisationTypeName FROM 
recruitment as re, ahig_services as ahs, salary_options as so, pay_roll as pr,
resource_legal as rl, training as tr, organisation as o INNER JOIN 
organization_type ot ON FIND_IN_SET(ot.id, o.organisationType) > 0 where 
re.organisationId=o.id AND ahs.organisationId=o.id AND so.organisationId=o.id 
AND pr.organisationId=o.id AND rl.organisationId=o.id AND 
tr.organisationId=o.id GROUP BY o.id,o.organisationType ORDER BY o.id ASC
limit 0, 10

Answer 1

To improve performance you should check the selectivity index of the columns on which joins are made. A proper indexed table will improve performance. for more info : http://beginner-sql-tutorial.com/sql-query-tuning.htm

You can also try indexed views for tables which can have some performance benefit. That said, SQL Server 2000 and above do have a special feature called Indexed Views that can greatly improve performance, but you have to create indexed views following a very specific set of guidelines(https://msdn.microsoft.com/en-us/library/ms187864.aspx). There is an important reference in Books Online in regards to view resolution(https://msdn.microsoft.com/en-us/library/ms190237.aspx).

Here is an article that describes the benefits and creation of indexed views:https://technet.microsoft.com/library/Cc917715