我有一个大查询(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
我使用 explain 命令并得到以下结果:
如何使查询更快?在大表中需要几秒钟。
"Showing rows 0 - 19 ( 20 total, Query took 3.5825 sec)"
据我所知,查询中使用的字段已经在每个表中建立了索引。
AuthLogTable 表已经设置了索引。
LIMIT
,你会得到多少行数据?) - mabi