0

I have a two statements that are basically identical. Yet, one takes 55 seconds, the other 2 seconds. WHY?

Here's the query that works fine:

SELECT
    employees.EmployeeID
FROM
    employees
LEFT JOIN req_budget_centers ON employees.BudgetCenter =    req_budget_centers.BudgetCenter
INNER JOIN req_bcjc ON employees.BudgetCenter = req_bcjc.BudgetCenter AND   employees.JobCode = req_bcjc.JobCode AND employees.EmpContr = req_bcjc.EmpContr
INNER JOIN j_bcjc_req ON req_bcjc.bcjcID = j_bcjc_req.bcjcID
INNER JOIN req_requirements ON j_bcjc_req.requirementID =   req_requirements.id
WHERE
    req_requirements.BoardCategoryID = 4 /*PROBLEM VALUE*/
    AND req_requirements.isActive = 1
    AND employees.`Status` = 'A'

Here's the code for the query that takes 50+ seconds:

SELECT
    employees.EmployeeID
FROM
    employees
LEFT JOIN req_budget_centers ON employees.BudgetCenter =    req_budget_centers.BudgetCenter
INNER JOIN req_bcjc ON employees.BudgetCenter = req_bcjc.BudgetCenter AND   employees.JobCode = req_bcjc.JobCode AND employees.EmpContr = req_bcjc.EmpContr
INNER JOIN j_bcjc_req ON req_bcjc.bcjcID = j_bcjc_req.bcjcID
INNER JOIN req_requirements ON j_bcjc_req.requirementID =   req_requirements.id
WHERE
    req_requirements.BoardCategoryID = 8 /* WORKS FINE */
    AND req_requirements.isActive = 1
    AND employees.`Status` = 'A'

Here are the execution plans for those two queries: enter image description here

There's nothing in the database or in the tables that's different for the value 8 vs 4... the number of overall records involved is the same... How do I hunt down the bottleneck here?

I would also add that leaving the condition ( = 8 or =4) out completely, but leaving all the joins in, speeds up the query considerably as well...

Sergiu Z
  • 153
  • 1
  • 3
  • 12
  • Maybe the one query has a higher cardinality that overflows your buffers and makes it run a lot slower. It's possible your MySQL server is not tuned very well. – tadman Dec 06 '17 at 02:11
  • @tadman "higher cardinality" and "overflows your buffers" flew extremely high over my head. I'd mention there's nothing else running on the server and there are barely any users on the server... 2-3 concurrent connections if that. The only thing that jumps at me is the "key_len" for the employees table... I don't understand why the key used is different for the two queries and why one is at 768 in length... what's 768 and why is it so long? – Sergiu Z Dec 06 '17 at 02:15
  • "Cardinality" here is a term that means "how many rows MySQL must consider at a time when navigating indexes and when doing your `JOIN` operations". In other words, if the second query involves a lot more data then it's possible MySQL doesn't have enough memory to keep it all in RAM and it has to page out to disk (use a temporary table), which is a lot slower. – tadman Dec 06 '17 at 02:17
  • Your server can be tuned, though, there's a lot of settings that govern how much memory is allocated to particular kinds of buffers. If you've never changed your server settings it's probably running on the factory defaults which are, honestly, pretty crappy. I've seen systems "tuned" as if the server itself has only 64 *megabytes* of memory. Some of these defaults that ship with different OS distributions are hilariously out of date. – tadman Dec 06 '17 at 02:18
  • @tadman if you have a link to a 'tuning' article, i'd be happy to try it out. Still wouldn't 'explain' show the number of records involved in putting everything together? Ironically the number of rows seems smaller in the problem query than the other one. – Sergiu Z Dec 06 '17 at 02:19
  • 1
    There's entire (large) books written on this subject, but a good place to start is, as always, [the documentation](https://dev.mysql.com/doc/refman/5.7/en/optimization.html) which talks about some of the knobs and dials you have and what they do. [MySQL Workbench](https://www.mysql.com/products/workbench/) is also a good tool to use as it can be used to monitor server performance and, from that, make recommendations on what to adjust. – tadman Dec 06 '17 at 02:20
  • By the way, if I leave all the joins but just remove the condition ( regarding boardcategory = 4 or 8) the query is very fast too... – Sergiu Z Dec 06 '17 at 02:23
  • Remember `EXPLAIN` makes a guess as to what *might* happen when the query is run. MySQL is notoriously bad at guessing, too. PostgreSQL, another popular, free database platform, actually executes the query and reports on what it did at each stage, even capturing the timing for each operation. MySQL has a lot of catching up to do here. – tadman Dec 06 '17 at 02:23
  • While some will tell you MySQL indexes don't need rebuilding I am not so convinced of this. Are your tables based MyISAM or InnoDB? Suggest you look at: https://stackoverflow.com/questions/321461/when-should-database-indexes-be-rebuilt and https://stackoverflow.com/questions/30051510/how-can-i-rebuild-indexes-and-update-stats-in-mysql-innodb – Paul Maxwell Dec 06 '17 at 02:59
  • @Used_By_Already I did think about indexes and I did run 'optimize' on the tables. In fact, I've dumped everything to a sql file and re-added everything to a new database and still the same problem. – Sergiu Z Dec 06 '17 at 16:09

1 Answers1

-1

I would suggest to pass the parameter and check the timing for value 4 and 8.

Example : how to pass the parameter. set @type = 'foo';

select * from Stuff where type=@type;

Sohan.Choudhury
  • 201
  • 2
  • 4