How to Optimize this MYSQL Query - Join several tables

Question

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:

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

Tag the dbms product used, because different products have different ways to optimize things. Also list indexes, foreign keys etc. And please try to format the query, it's not easy to read that code... — jarlh, Oct 06 '15 at 09:40
Have you tried running [ANALYZE TABLE](http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html) for the tables included in the query? Seems like estimates as to what needs to be sorted using filesort is off (how many rows would you get w/o the `LIMIT`?) — mabi, Oct 06 '15 at 10:02

score 3 · Accepted Answer · edited May 23 '17 at 11:52

3

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

edited May 23 '17 at 11:52

Community

1
1

answered Oct 06 '15 at 09:58

borowis

1,207
10
17

4

then consider indexing on those fields. but please take into account that index has performance penalty for inserts + some requirements for storing index data. You can add BTREE index in mysql on AuthDate, AuthTime for example. For more details please see MySql documentation https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html – borowis Oct 06 '15 at 10:07
yeah, I see. more information is given here https://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html. Seems like the problem here is `in some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: 1. You use ORDER BY on different keys`. What you now can try is to add index on both fields, and make sure that `Using filesort;` is gone from explain output. – borowis Oct 06 '15 at 10:15
well, I finished the article on ORDER BY optimization, and I can't say if adding index on both fields will work, but that's a thing to try. If it doesn't work, then the query should be re-organized in some way (the idea is to get rid of slow filesort) or maybe you can sort data in your application code – borowis Oct 06 '15 at 10:28
I tried adding index on both fields, and as Borys Zibrov said, it doesn't work. – justyy Oct 06 '15 at 10:28
yeah, that's unfortunate. you can sort in code then or you'll have to re-write query (it's hard to say in which way exactly, I suggest you a) try to make AuthLogTable first non-const type table in your query execution plan (like can you join it from PoolTable?) I'm not sure it's possible b) try to experiment with subqueries and look for SO 'order by optimization' questions) – borowis Oct 06 '15 at 11:05

Rahul · Answer 2 · 2015-10-06T09:56:41.460

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.

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;

edited Oct 06 '15 at 09:56

answered Oct 06 '15 at 09:47

Rahul

76,197
13
71
125

I was going to accept your answer until I run your query and it takes two times slower... "Showing rows 0 - 19 ( 20 total, Query took 7.4764 sec)" – justyy Oct 06 '15 at 09:53
well, i may not be correct, the timings vary.. but from the explain, it gives no difference than the query in the question – justyy Oct 06 '15 at 09:57
@DoctorLai, try again, made a little change in joins order. Also make sure that the column which you are joining are all indexed properly. – Rahul Oct 06 '15 at 09:58
@jarlh, sometimes it may be if there is no ON clause but yes it may not be but it's always better to have proper way. From other answer I see OP said removing the order by makes the query faster which make me guess that he don't have a index on order columns. Now, OP didn't place the DDL schema and so I can't point that in specific rather do mentioned to index all necessary column. Hope that make sense. – Rahul Oct 06 '15 at 10:36

score 0 · Answer 3 · answered Oct 06 '15 at 10:05

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

Do you have a fiddle to back up that this in fact uses a different execution plan? Can you explain what in the `explain` makes you think the optimizer isn't taking care of the joins correctly? — mabi, Oct 06 '15 at 10:09
@mabi, as you asked, I don't have the table set up to do an explain the query. Have asked to `try` and didn't say it the most optimized one. — James Jithin, Oct 06 '15 at 11:08

How to Optimize this MYSQL Query - Join several tables

3 Answers3