CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Michael Nguyen
Michael Nguyen

Reputation: 1752

same database and query - production server is 100 times slower than local environment

i am struggling to understand why production machine executes a query from PHPMYADMIN so slow compared to local machine.

The query I ran to get the number of job each skill has

SELECT jrs.skill_id, COUNT(jrs.job_id)
FROM
(
SELECT id
FROM job
WHERE active_to > NOW()
) as active_job
JOIN job_req_skill jrs 
WHERE jrt.job_id = active_job.id
GROUP BY skill_id

the database is exact the same on both production and local machine and no heavy traffic on the production server that takes away mysql resources.

Local server ( Win 7 with AMPPS , mysql ver 14.14 distrib 5.6.14 for win32 (x86), PhpMyAdmin v3.5.8.2)

Production Server ( Ubuntu 12.04 LTS, mysql ver 14.14 Distrib 5.5.35, for debian-linux-gnu, PhpMyAdmin v3.4.10.1)

Why is there such huge difference in performance ???

Thanks

Upvotes: 0

Views: 1344

Answers (2)

dkretz
dkretz

Reputation: 37645

How many rows are you returning? It might be counting the time it takes to transmit the results from the host to your workstation.

Upvotes: 1

Barmar
Barmar

Reputation: 780723

You're running MySQL version 5.5 in production, 5.6 locally. They obviously improved query optimizations in 5.6. Notice the different in the EXPLAIN output.

Try this:

SELECT jrs.skill_id, COUNT(*)
FROM job 
JOIN job_request_skill AS jrs ON jrs.job_id = job.id
WHERE job.active_to > NOW()
GROUP BY jrs.skill_id

Referring to the table directly, rather than in a subquery, may avoid constructing a temporary table. And you should generally use COUNT(*) rather than COUNT(column) unless you specifically need to avoid counting null values in the column; if there's an index on skill_id, it may then be able to use this to get the counts.

Upvotes: 3

Related Questions