Answer the question
In order to leave comments, you need to log in
How to correctly compose Mysql Count and the correct query to the database including 3 tables?
MYSQL + PHP
There are 2 tables.
1. Services rendered (350,000 records)
| id | id_service | id_worker | usluga_okazana
| 1 | 2 | 3 | 1
2. Directory of services (500 entries)
(in the same table, a directory of Employees - the general table of directories is so large)
| id | name | category |
| 2 | nail shelf | usluga |
| 3 | Vasily | workers |
Tell me how to better and correctly write a query, the result of which will be:
you need to find out the number (how many) Vasily performed each service from the directory, if Vasily did not perform a certain service - 0. then you do not need to display the service.
those.
Vasiliy
Snow removal - 3 times
Hung a picture - 4 times
Room cleaning - 0 (not necessary, do not show)
Petrovich
Snow removal - 1 time
Hung a picture - 55 times
Room cleaning - 233
All the difficulty I have is that
Vasiliev = 500
Services = 200
A marks about execution = 350,000 lines
Everything is done on Count() - I heard that it loads the database very much, and it is.
Is it possible to execute this query without Count ?
I'm ashamed to show my current requests, they hang up the server a lot.
Answer the question
In order to leave comments, you need to log in
Make sure you have an index on two fields at once (job_id, worker_id => id_uslugi , id_rabotnik) A query on such a small table should be fast enough
SELECT c.name, b.name, count(*) from unslugi as a
left join info as b on a.job_id=b.id
left join info as c on a.worker_id=c.id
WHERE a.usluga_okazana=1
GROUP BY a.job_id, a.worker_id;
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question