next »

[Antoine T]

Hi,

I used VirtueMart for huge web sites (at the moment three with more than 15000 rows in the #__vm_product table).

The default back office is painly slow and I think the way developpers can browse the category tree is not efficient enough.

That is why I created a new table. It is for instance used in shop_browse_queries, but it can also be used to make the creation of category menu structure in the front more efficient, (same thing for the creation of the categories select/options widget).

Here is the table I am using at the moment :

CREATE TABLE IF NOT EXISTS `jos_vm_category_tree` (
  `category_id` int(11) NOT NULL,
  `category_path` varchar(255) NOT NULL,
  `level` int(11) NOT NULL
  KEY `category_id` (`category_id`),
  KEY `category_path` (`category_path`),
  KEY `level` (`level`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The category path contains "the bread crumb" of the category.

Here is a sample row :
category_id   category_path     level
25983           ;5538;5544;5669;  4

This way, if one want to know what are the categories (the products...) which are below the category 5538, it may uses (...) like ';5538;%'

If you whant to build the breadcrumb of 25983 (for SEO for instance), it is also very obvious.

But it can be improved in order to help the build of widgets and menus. I will explain the details if I see the community is interested.

Regards

Antoine Tissier

PS  : sorry for my pathetic english
PS2 : I made the same thing for browsing through attributes

[bunak]

Hi
can you get the urls of this sites, which have more then 15000 products.

[Antoine T]

Sorry, I am not allowed to. (the company I am working for wants to avoid script-kiddies).

[IvyPearson]

Hi,

 Thank you, this is a very useful idea and it would be great if it was a built-in table in VM.
Actually, it is quite painful to retrieve a full path in order to make a breadcrumb or to apply a specific color/design for a category (and of course the products related to that category).

[klutzon]

Hey Antoine,

I'm very interested in working with you to design and test some code that will improve VirtueMart, if you've got the time to work with me. Send me a PM if you do have the time.

[Antoine T]

Great ! I have just bought a new house, ang got some huge work to make inside it, but it'll send you code ASAP.

[klutzon]

Great ! I have just bought a new house, ang got some huge work to make inside it, but it'll send you code ASAP.

I appreciate your enthusiasm. Take your time, sort out your house and your work first, and when you've got time, send me a PM and we'll take it from there.

[Antoine T]

I am starting to post code about this performance enhancement on my blog about virtuemart and joomla.

A the moment, one can only find SQL snippets.

Regards

Antoine

[Fredrik Larsson]

Antoine, your blog doesn't work?

I am working on a site with 22000 products and roughly 7000 categories and it will probably grow to around 100000 products. A nightmare I know.. anyway I don't think that a new table is what is needed but more an improvement in the PHP-code. On the frontpage I am using mod_virtuemart_product_categories that displays the categories using XHTML. This tree is very fast and I would guess that a similar approach could be used to lets say have a button that on a click pops up a treeview in plain text where you select the category by clicking on it. This would populate the input-field with the category_id. Pressing search will filter out the products.

The way categories are used by populating a dropdownbox today is a real showstopper on large sites.

[Antoine T]

Hi,

I know. (no space left on the device :-()). It is fixed now.

The thing is : we didn't used virtuemart as in. (no probleme with select, options...).

Our performance issues are essentially in the back office (not used by our customer anyway).

It is far from being easy with the default data model to change shop_browse_queries.php in order to show the products stored in a category and in its childs (there is an unlimited depth level of categories), and sort the result.

Regards

Antoine

[makuwood]

Hi Antoine,
Nice! I also will PM you about it.