Large Hierarchical Database in MySql

Hi.
I have this large database of more than 600000 rows. which I need to show in a hierarchy. eg Widget->Blue Widget->Large Blue Widget and Widget->Blue Widget->Small Blue Widget etc. Due to this large database I cannot read it very quickly. Does anyone know a way to handle this using mysql and php?

 

Your not specific with your question.
a.) Speed up your database by creating indexes. If you create a lot of queries with widget + color then create a composite index on this item.
b.) Hierarchical databases - sitepoint has articles on this

 

I am trying to display this rather large category list like yahoo directory.

 

You might create another DB just to hold your indexes, that'll speed it up a lot.

 

There was an article a while ago on SP that had detailed instructions on to create a very quick hierarchical table using special de-normalized data values. I'll see if I can dig it up...

Owen

 

I read the acticle at sitepoint. Thanks. I figured out a way myself. Thanks for the indexing idea.

 

some tips
1. render complex pages to static cache
2. use mysql stored procedures to speedup data retriveal
3. tune mysql - key cache, query cache etc

 

If you have the processing power, you could simply read the DB into a multi-dimensional array and use recursion to display the tree with say, PHP.

 

Not being a PHP developer I'm not sure if this is possible but I had a similar problem in ASP/ASP.net with MS SQL.

I try to offload from the database into the business logic where applicable.

If you want to do it on the fly I tend to use a hash table and populate it with a simplified Hierarchy which I then use to calculate my breadcrumb locations rather than the database itself. Using this method means you only hit the database once and hash tables are very fast.

To reduce the load further I cached the hash table and added some code to my update / add methods in the business objects so that when a new item was added or updated the cache was refreshed.

Another option I considered was simplfying complex Hierarchies in the database and saving them in a secondary format using triggers to maintain them. This works great when you have lots of SELECTs and fewer INSERTS / UPDATES however if you do a lot of updating this could compound the issue.

One way to do this is to create a varchar field to store the precaculated breadcrumb.
Another way which is more intensive on the read was to have a flattened version of the heirachial structure.

e.g.

tbl_FlatHierarchy
TopLevelID
ItemId
Level

sample data would be

1 3 0
1 4 1
1 5 2
3 2 0
3 4 1
3 7 2
3 9 3

Because the hierachy is now in this flatter structure you can do a simple select to obtain your breadcrumb records

SELECT tbl_Items.*
FROM tbl_Items INNER JOIN tbl_FlatHeirachy
ON tbl_Items.ItemID = tbl_FlatHierarchy.ItemId
WHERE TopLevelId = 3
ORDER BY Level

It also simplfies agregation of Hierarchies.