CODEWITHSUNDEEP
mysqloptimizationindexing
Anthony
Anthony

Reputation: 3218

Mysql query optimization

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!

Upvotes: 0

Views: 89

Answers (2)

Jacob
Jacob

Reputation: 383

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

Upvotes: 1

Thomas
Thomas

Reputation: 64674

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
                    )

Upvotes: 0

Related Questions