mysql - 巨大的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-15 MB。

感谢帮助!

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