如何优化这个MYSQL查询-加入几个表?

I have a big query (MYSQL) to join several tables:

我有一个大查询(MYSQL)来连接几个表:

SELECT * FROM
    `AuthLogTable`,
    `AppTable`,
    `Company`,
    `LicenseUserTable`,
    `LicenseTable`,
    `LicenseUserPool`,
    `PoolTable` 

WHERE
    `LicenseUserPool`.`UserID`=`LicenseUserTable`.`UserID` and 
    `LicenseUserTable`.`License`=`LicenseTable`.`License` and 
    LEFT(RIGHT(`AuthLogTable`.`User`, 17), 16)=`LicenseUserPool`.`UserID` and 
    `LicenseUserPool`.`PoolID`=`PoolTable`.`id` and 
    `Company`.`id`=`LicenseTable`.`CompanyID` and 
    `AuthLogTable`.`License` = `LicenseTable`.`License` and 
    `AppTable`.`AppID` = `AuthLogTable`.`AppID` AND 
    `PoolTable`.`id` IN (-1,1,2,4,15,16,17,5,18,19,43,20,3,6,8,10,29,30,7,11,12,24,25,26,27,28,21,23,22,31,32,33,34,35,36,37,38,39,40,41,42,-1)

ORDER BY 
     `AuthLogTable`.`AuthDate` DESC，
     `AuthLogTable`.`AuthTime` DESC

LIMIT 0,20

I use explain and it gives the following:

我使用explain，它给出如下内容:

How to make this faster? It takes several seconds in a big table.

如何使它更快?一张大桌子需要几秒钟的时间。

"Showing rows 0 - 19 ( 20 total, Query took 3.5825 sec)"

“显示第0 - 19行(总共20行，查询时间3.5825秒)”

as far as i know, the fields used in the query are indexed in each table.

据我所知，查询中使用的字段在每个表中都被索引。

Indices are set for AuthLogTable

为AuthLogTable设置索引

3 个解决方案

#1

You can try running this query without 'order by' clause on your data and see if it makes a difference (also run 'explain'). If it does, you can consider adding index/indices on the fields you sort by. Using temporary; using filesort; means that the temp table is created and then sorted, without index that takes time.

您可以尝试在数据上运行这个查询而不使用“order by”子句，并查看它是否有影响(也可以运行“explain”)。如果有，您可以考虑在您排序的字段上添加索引/索引。使用临时的;使用filesort;意味着创建了临时表，然后进行排序，没有索引，这需要时间。

As far as I know, join style doesn't make any difference because query is parsed into another form anyway. But you still may want to use ANSI join syntax (see also this question ANSI joins versus "where clause" joins).

据我所知，join样式没有任何区别，因为查询被解析为另一种形式。但是您可能仍然希望使用ANSI join语法(请参见ANSI join vs . where子句联接)。

#2

First of all consider modifying your query to use JOINS properly. Also, make sure that you have indexed the columns used in JOIN ON clause ,WHERE condition and ORDER BY clause.

首先，考虑修改查询以正确使用连接。另外，请确保您已经索引了JOIN ON子句中使用的列，其中的条件和ORDER BY子句。

select * from `AuthLogTable`
join `AppTable` on `AppTable`.`AppID` = `AuthLogTable`.`AppID`
join  `LicenseTable` on `AuthLogTable`.`License` = `LicenseTable`.`License`
join `Company` on `Company`.`id`=`LicenseTable`.`CompanyID`
join `LicenseUserTable` on `LicenseUserTable`.`License`=`LicenseTable`.`License`
join `LicenseUserPool` on `LicenseUserPool`.`UserID`=`LicenseUserTable`.`UserID`
join `PoolTable`  on `LicenseUserPool`.`PoolID`=`PoolTable`.`id`
where LEFT(RIGHT(`AuthLogTable`.`User`, 17), 16)=`LicenseUserPool`.`UserID`
and `PoolTable`.`id` IN (-1,1,2,4,15,16,17,5,18,19,43,20,3,6,8,10,29,30,7,11,12,24,25,26,27,28,21,23,22,31,32,33,34,35,36,37,38,39,40,41,42,-1)
order by `AuthLogTable`.`AuthDate` desc,  `AuthLogTable`.`AuthTime` desc 
limit 0,20;

#3

Try the following query:

试试下面的查询:

SELECT *
FROM `AuthLogTable`
JOIN `AppTable` ON (`AppTable`.`AppID` = `AuthLogTable`.`AppID`)
JOIN `LicenseUserPool` ON (LEFT(RIGHT(`AuthLogTable`.`User`, 17), 16)=`LicenseUserPool`.`UserID`)
JOIN `LicenseUserTable` ON (`LicenseUserPool`.`UserID`=`LicenseUserTable`.`UserID`)
JOIN `LicenseTable` ON (`AuthLogTable`.`License` = `LicenseTable`.`License`
                        AND `LicenseUserTable`.`License`=`LicenseTable`.`License`)
JOIN `Company` ON (`Company`.`id`=`LicenseTable`.`CompanyID`)
JOIN `PoolTable` ON (`LicenseUserPool`.`PoolID`=`PoolTable`.`id`)
WHERE `PoolTable`.`id` IN (-1,1,2,4,15,16,17,5,18,19,43,20,3,6,8,10,29,30,7,11,12,24,25,26,27,28,21,23,22,31,32,33,34,35,36,37,38,39,40,41,42,-1)
ORDER BY `AuthLogTable`.`AuthDate` DESC， `AuthLogTable`.`AuthTime` DESC LIMIT 0,20

#1