CODEWITHSUNDEEP
mysqldatabase
ononononon
ononononon

Reputation: 1083

mysql Slow query issue

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 enter image description here

Database_2 enter image description here

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

Upvotes: 0

Views: 109

Answers (1)

Imran Zahoor
Imran Zahoor

Reputation: 2787

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.

Upvotes: 1

Related Questions