CODEWITHSUNDEEP
mysqlsql
justyy
justyy

Reputation: 6041

How to Optimize this MYSQL Query - Join several tables

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

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:

enter image description here

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

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

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

Indices are set for AuthLogTable

enter image description here

Upvotes: 3

Views: 88

Answers (3)

James Jithin
James Jithin

Reputation: 10565

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

Upvotes: 0

borowis
borowis

Reputation: 1235

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.

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).

Upvotes: 3

Rahul
Rahul

Reputation: 77866

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.

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;

Upvotes: 2

Related Questions