0

Have follow query:

  SELECT tbl_yaca_sites.`id_site` FROM `tbl_yaca_sites`
   INNER JOIN `tbl_relation` ON tbl_relation.id_website = tbl_yaca_sites.id_site
   INNER JOIN `tbl_yaca_directories` ON tbl_yaca_directories.id_record = tbl_relation.id_category
   INNER JOIN `tbl_applications` ON tbl_applications.id_site = tbl_yaca_sites.id_site
  where
  tbl_applications.id_application IN (41)
  and tbl_yaca_directories.type = 0
  AND tbl_yaca_directories.work = 1
  AND tbl_relation.work = 1  GROUP BY tbl_yaca_sites.`id_site`

There are many indexies:

  1. tbl_yaca_sites - for primary id_site and another one id_site (the same field, but another index)
  2. tbl_relation - for primary ID and combined index (id_site, id_category, work)
  3. tbl_yaca_directories - for primary id_record and combined index 'type' (type, work)
  4. tbl_applications - there are not primary. Has one index id_application.

The query works good when I setup described indexies only (but without 'group by'). I tested it many times.

So, if I run this query without group by it takes one second. Enough good result, where are about 200 000 records in each table. Added 'group by', always takes 16 seconds.

if I do follow operation:

SELECT id_site FROM (SELECT tbl_yaca_sites.`id_site` FROM `tbl_yaca_sites`
   INNER JOIN `tbl_relation` ON tbl_relation.id_website = tbl_yaca_sites.id_site
   INNER JOIN `tbl_yaca_directories` ON tbl_yaca_directories.id_record = tbl_relation.id_category
   INNER JOIN `tbl_applications` ON tbl_applications.id_site = tbl_yaca_sites.id_site
  where
  tbl_applications.id_application IN (41)
  and tbl_yaca_directories.type = 0
  AND tbl_yaca_directories.work = 1
  AND tbl_relation.work = 1 ) as tbl GROUP BY id_site

it takes 1,7 seconds.

This is explain query:

1, 'SIMPLE', 'tbl_yaca_sites', 'index', 'PRIMARY,id_site', 'PRIMARY', '4', '', 102358, 'Using index'
1, 'SIMPLE', 'tbl_applications', 'ref', 'pair', 'pair', '8', 'webservices.tbl_yaca_sites.id_site,const', 1, 'Using index'
1, 'SIMPLE', 'tbl_relation', 'ref', 'comb', 'comb', '4', 'webservices.tbl_yaca_sites.id_site', 1, 'Using where; Using index'
1, 'SIMPLE', 'tbl_yaca_directories', 'eq_ref', 'PRIMARY,type', 'PRIMARY', '4', 'webservices.tbl_relation.id_category', 1, 'Using where'

Also tried to add count(*) | count(id_site) | count(1). result the same.

Why so long? Could anybody tell me what I have missed up?

Thanks everybody in advance!

Improve this question

2 Answers 2

Reset to default
1

You might also try:

SELECT DISTINCT tbl_yaca_sites.`id_site`
FROM `tbl_yaca_sites`
INNER JOIN `tbl_relation` ON tbl_relation.id_website = tbl_yaca_sites.id_site
INNER JOIN `tbl_yaca_directories` ON tbl_yaca_directories.id_record = tbl_relation.id_category
INNER JOIN `tbl_applications` ON tbl_applications.id_site = tbl_yaca_sites.id_site
WHERE
     tbl_applications.id_application IN (41)
     AND tbl_yaca_directories.type = 0
     AND tbl_yaca_directories.work = 1
     AND tbl_relation.work = 1
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Almost the same result. 5 seconds when I run it for the first time. And 1,5 seconds - follow times. But it is better than nested query:)
Generally, the first time you run the query, SQL will create the execution plan, which causes it to run a bit slower. Every time you run it after that, it will use a cached execution plan... and that's going to be the general speed of the query (assuming you end up saving it as a stored procedure). Is 1.5 seconds too slow, in this case?
to be honest, I don't know is it a good result? Thinking a good result is when a query takes less than one second, isn't it? Is it possible to save time for join queries up to one second?
To speed it up beyond that, it would probably require the addition of some indexes. One way (that I often use) to figure out what exactly is causing the slow-down, is to start by commenting out all the various inner joins and where clauses, and then running the query... and measuring how long it takes. Then, add each inner join and/or where clause back in one-at-a-time, and you should then be able to see which one is causing it to slow down. That will give you a clue which index needs to be added or rebuilt (if that is, in fact, the issue).
I see. I did it for query without 'group by'. It worked perfect. After adding 'group by'. It takes time writ double.
|
0

Given that you only want a unique list of id_site values, you might be able to just use an In clause:

Select id_site
From tbl_yaca_sites 
Where id_site In    (
                    Select id_site
                    From tbl_yaca_sites
                        Join tbl_relation 
                            On tbl_relation.id_website = tbl_yaca_sites.id_site
                        Join tbl_yaca_directories 
                            On tbl_yaca_directories.id_record = tbl_relation.id_category
                        Join tbl_applications 
                            On tbl_applications.id_site = tbl_yaca_sites.id_site
                    Where tbl_applications.id_application In(41)
                        And tbl_yaca_directories.type = 0
                        And tbl_yaca_directories.work = 1
                        And tbl_relation.work = 1
                    )
Improve this answer

1 Comment

Thank you for your answer. The query takes 26 seconds:(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.