COUNT with two joins (unlimited hierarchy)

[nego78]

hi

i've got two tables
Localization (Localization_Id,Localization_Left,Localization_Right) - indexes on Localization_Id PRI and on (Localization_Left,Localization_Right)

Company (Company_Id,Localization_Id) - indexes on Company_Id PRI and another one for Localization_Id

I use this query to count companies in each localization and count it for all parents of localization

SELECT l1.Localization_Id,COUNT(f.Company_Id) FROM Localization AS l1 LEFT JOIN Localization AS l2 ON l2.Localization_Left > l1.Localization_Left AND l2.Localization_Right < l1.Localization_Right LEFT JOIN Company AS f ON f.Localization_Id=l2.Localization_Id AND f.Company_Status >= 10 GROUP BY l1.Localization_Id

And then UPDATE each.

This is too slow because in Localization is over 55000 records and in Company about 39000

Have you any ideas how to repair this ?

 

[nego78]

So you don't have ideas or it's best solution ?

 

[DonQuichote]

You have seen hierarchical database models like these?

http://www.sitepoint.com/article/hierarchical-data-database/

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[nego78]

Yes i know and in this example i use nested model - left and right columns.

But problem is not with usage but with counting companies belonging to each localization.

when i've got:

Name, Left, Right
First, 1 , 6
  Inside1 2,3
  Inside2 4,5
Second, 7,8

I need to find how many companies belong to "Inside1" and "Inside2" and than how many in First


The query which i wrote in first post is good - it counts but it taking to long

 

[towerbase]

What do you mean "This is too slow"? How long does it take and how long do you think it should take.

Poor performance in MySQL is frequently caused by not indexing properly. How are the indexes actually defined in the localization table?

It looks like you have one index for (Localization_Left,Localization_Right). If this is the case then I suggest you create two indexes. One for Localization_Left and another for Localization_Right.

Perfomance can sometimes be improved by changing the MySQL start up parameters. Check out MySQL Administrator.

Andrew
Hampshire, UK