海量SQL语句优化

Question

以下语句在每个查询中运行11到12秒。我试着添加索引和其他东西，但现在我不知道下一步该做什么。

这是codeigniter中的“搜索”。

   SELECT produktlisten.id as id,
          produktlisten.name as name,
          produktlisten.alias,
          users_company.name as company_name,
          produkte.id as produkte_id,
          produkte.name as produkte_name,
          sliderpic,
          produktlisten.description as description,
           MATCH (produktlisten.name,
                  produktlisten.description,
                  produktlisten.tags) AGAINST ('<<SEARCH TERM>>' )
          + MATCH (users_company.name,
                   users_company.description) AGAINST ('<<SEARCH TERM>>' )
          + MATCH (produkte.name,
                   produkte.description,
                   produkte.tags) AGAINST ('<<SEARCH TERM>>' ) AS relevanz
     FROM produktlisten
LEFT JOIN produkte ON produktlisten.id = produkte.produktlisten_id
      AND produkte.active = 1
LEFT JOIN stilrichtung ON produktlisten.stil_id = stilrichtung.id
LEFT JOIN users_company ON produktlisten.company_id = users_company.id
    WHERE produktlisten.active =  '1'
      AND (MATCH (produktlisten.name,
                  produktlisten.description,
                  produktlisten.tags) AGAINST ('<<SEARCH TERM>>')
           OR MATCH (produkte.name,
                     produkte.description,
                     produkte.tags) AGAINST ('<<SEARCH TERM>>')
           OR MATCH (users_company.name,
                     users_company.description) AGAINST ('<<SEARCH TERM>>' ))
 GROUP BY id
 ORDER BY relevanz DESC
 LIMIT 21   

数据库很大，表也很大，每个表都有5-15MB。

感谢您的帮助！

Answer 1

我不是一个喜欢MySQL的人，所以我想给你一些提示。如果有什么能用的话就好了。

    SELECT produktlisten.id as id,
          produktlisten.name as name,
          produktlisten.alias,
          users_company.name as company_name,
          produkte.id as produkte_id,
          produkte.name as produkte_name,
          sliderpic,
          produktlisten.description as description,
           MATCH (produktlisten.name,
                  produktlisten.description,
                  produktlisten.tags) AGAINST ('<<SEARCH TERM>>' )
          + MATCH (users_company.name,
                   users_company.description) AGAINST ('<<SEARCH TERM>>' )
          + MATCH (produkte.name,
                   produkte.description,
                   produkte.tags) AGAINST ('<<SEARCH TERM>>' ) AS relevanz
     FROM produktlisten
LEFT JOIN produkte ON produktlisten.id = produkte.produktlisten_id 
LEFT JOIN stilrichtung ON produktlisten.stil_id = stilrichtung.id
LEFT JOIN users_company ON produktlisten.company_id = users_company.id
    WHERE produktlisten.active =  '1' AND produkte.active = 1
      AND (MATCH (produktlisten.name,
                  produktlisten.description,
                  produktlisten.tags) AGAINST ('<<SEARCH TERM>>')
           OR MATCH (produkte.name,
                     produkte.description,
                     produkte.tags) AGAINST ('<<SEARCH TERM>>')
           OR MATCH (users_company.name,
                     users_company.description) AGAINST ('<<SEARCH TERM>>' ))
 GROUP BY id
 ORDER BY relevanz DESC
 LIMIT 21   

或者，如果它来自SQL Server，那么我会试试子查询来限制行数，以获得更好的性能，如下所示。

               SELECT produktlisten.id as id,
          produktlisten.name as name,
          produktlisten.alias,
          users_company.name as company_name,
          produkte.id as produkte_id,
          produkte.name as produkte_name,
          sliderpic,
          produktlisten.description as description,
           MATCH (produktlisten.name,
                  produktlisten.description,
                  produktlisten.tags) AGAINST ('<<SEARCH TERM>>' )
          + MATCH (users_company.name,
                   users_company.description) AGAINST ('<<SEARCH TERM>>' )
          + MATCH (produkte.name,
                   produkte.description,
                   produkte.tags) AGAINST ('<<SEARCH TERM>>' ) AS relevanz
     FROM  
     (
     Select * from produktlisten where active = '1'
      AND (MATCH (produktlisten.name,
                  produktlisten.description,
                  produktlisten.tags) AGAINST ('<<SEARCH TERM>>')
           OR MATCH (produkte.name,
                     produkte.description,
                     produkte.tags) AGAINST ('<<SEARCH TERM>>')
           OR MATCH (users_company.name,
                     users_company.description) AGAINST ('<<SEARCH TERM>>' ))    
     ) produktlisten
LEFT JOIN produkte ON produktlisten.id = produkte.produktlisten_id AND produkte.active = 1
LEFT JOIN stilrichtung ON produktlisten.stil_id = stilrichtung.id
LEFT JOIN users_company ON produktlisten.company_id = users_company.id
 GROUP BY id
 ORDER BY relevanz DESC
 LIMIT 21