mysql Slow query issue

Question

I have the following problem:

There are 2 MySQL databases Database_1 which is the original one and Database_2, which is based on the first database with only few changes (some new columns, different data).

When i run a query which involves 3 tables from database i get very different performance results. I was testing both databases on the same localhost server.

Query:

SELECT `Applicant`.`id`, `Applicant`.`name`, `User`.`login`, `User`.`id`, Project.id 
FROM `applicants` AS `Applicant`
LEFT JOIN `authake_users` AS `User` ON (`Applicant`.`authake_user_id` = `User`.`id`)
LEFT JOIN `projects` AS `Project` ON (`Project`.`applicant_id` = `Applicant`.`id`)
WHERE `User`.`login` LIKE _latin1 '%1000%' AND `Project`.`id` IS NULL;

Query execution time:

Database_1 – 0.3 s – this database is the larger one though it’s faster

Database_2 – 1 min 40 s

All 3 tables are using MyISAM engine. Tables applicants and projects have charset utf8, table authake_users has charset latin1.

I’ve checked the indexes (both databases have exactly the same indexes), rebuilt them and used ANALYZE and OPTIMIZE on all 3 tables with no success.

The only difference i’ve found is show below, when i run the query with EXPLAIN command in front:

Database_1

Database_2

Does anybody have any idea what to look for? What can be causing such a difference in the performance?

Did you get a chance to check my answer below? Please try it out and do update. — Imran Zahoor
– Imran Zahoor, Commented Feb 2, 2017 at 14:05

Imran Zahoor · Accepted Answer · 2017-02-01 10:58:08Z

Remember that MySql query analyzer chooses optimization plan based on available resources memory. But changing order of the tables and their JOIN plans do help it to choose a better plan.

I don't have access to the database plus I'm not aware of the output that you require from this query, that's why cannot perform analysis. But following are some suggestions you can try out

Your query without WHERE

SELECT `Applicant`.`id`, `Applicant`.`name`, `User`.`login`, `User`.`id`, Project.id 
FROM `applicants` AS `Applicant`
LEFT JOIN `authake_users` AS `User` 
    ON `Applicant`.`authake_user_id` = `User`.`id` 
    AND `User`.`login` LIKE _latin1 '%1000%'
LEFT JOIN `projects` AS `Project` 
    ON `Project`.`applicant_id` = `Applicant`.`id` 
    AND `Project`.`id` IS NULL;

By changing the order of JOINS and without WHERE

SELECT `Applicant`.`id`, `Applicant`.`name`, `User`.`login`, `User`.`id`, Project.id 
FROM `authake_users` AS `User` 
LEFT JOIN `applicants` AS `Applicant` 
    ON `Applicant`.`authake_user_id` = `User`.`id` 
    AND `User`.`login` LIKE _latin1 '%1000%'
LEFT JOIN `projects` AS `Project` 
    ON `Project`.`applicant_id` = `Applicant`.`id` 
    AND `Project`.`id` IS NULL;

Your query by changing order of JOINS - 1

SELECT `Applicant`.`id`, `Applicant`.`name`, `User`.`login`, `User`.`id`, Project.id 
FROM `authake_users` AS `User`
LEFT JOIN `applicants` AS `Applicant` 
    ON `Applicant`.`authake_user_id` = `User`.`id`
LEFT JOIN `projects` AS `Project` 
    ON `Project`.`applicant_id` = `Applicant`.`id`
WHERE `User`.`login` LIKE _latin1 '%1000%' AND `Project`.`id` IS NULL;

Your query by changing order of JOINS - 2

SELECT `Applicant`.`id`, `Applicant`.`name`, `User`.`login`, `User`.`id`, Project.id 
FROM `projects` AS `Project`
LEFT JOIN `applicants` AS `Applicant` 
    ON `Project`.`applicant_id` = `Applicant`.`id`
LEFT JOIN `authake_users` AS `User` 
    ON `Applicant`.`authake_user_id` = `User`.`id` 
WHERE `User`.`login` LIKE _latin1 '%1000%' AND `Project`.`id` IS NULL;

Plus if you want to get benefit of Indexes fully such kind of User.login LIKE _latin1 '%1000%' conditions should be avoided. Better to use something like this if you really require it User.login LIKE _latin1 '1000%'.

I would be very much interested if you could also post the EXPLAIN of the queries I suggested.

Thank you, really the "changing order of JOINS - 1" solution fasten the query significantly
Cool! And it'll be even more better if you could share their EXPLAIN details as well :)

Collectives™ on Stack Overflow

mysql Slow query issue

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related