Why is VirtueMart so slow?

[Milbo]

It seems you just ignored what I wrote.

In short words. You cannot write a perfect table layout optimised for any case. It is always a compromise.

BTW: Version vm26 has a more finegrained selection. At one point the general rant was okey. We unnecessary joined the manufacturer table sometimes. This is removed. But we still use the same table layout.

a database design is always a compromise between
- Normalisation
- Data reduncy (influences also used space on the hdd)
- Selection performance

The more a database is normalised, the more tables you get. The less data redundancy you have, the more the selection performance goes down. You do not have multiple manufacturers per category, so you do not need this normalisation, you also dont want to search for manufacturers, then remove it.

... In case we would just add a manufacturer row to the product table, ...? We would have two rows, exact the same except the manufacturer. =>redundant data. We store it in a relation table as the normalisation rules say, no redundant data, searchable, but needs an extra JOIN. Please read here http://en.wikipedia.org/wiki/Denormalization

... VM is optimised ... for the majority. There are a lot more people using the multiple manufacturers feature than people having problem with the performance. VirtueMart is a Beetle VolksWagen, if you wanna use it as Pickup, you must invest something, if you want to use it as racing car, you must tune it for this purpose.

And Yes ill build you the table to test with, but the fix is so easy you could write it on the back of your hand like a chicks phone number.

Ehrm, and why you tell us this instead of just giving the fix? It seems you do not understand how an opensource project works. The idea of opensource projects is exactly that people use the code and donate something back. Some people give support, others test and some others return fixes, some people organise, others do marketing and so on.

[balai]

tampasounds i like your post but you might missing some other aspects which are important according to my humble opinion.

1. How will you deal with 1 x many relationships? Possibly you will have to repeat the same rows again and again creating an enormous table
2. How you can maintain a table which is not normalized?
For example you can work with keys when the tables are normalized but when this does not happen you will possibly have to repeat the same values. Right?

So lets say that you want to update a manufacturer name of a manufacturer used in x products.
Without normalization that means that you have to update all the x product records?


What i mean is that reading the db and the performance of this is just one aspect and requirement but not the only one. You have to deal with others as well

[mcaves]

I may be a little late, but I think the best way to go for such a large store is to go VPS at the very least. Once that`s installed, optimize you images with JCH and install Varnish to go along with your server. There are also cdn options that will make things even faster.

[Milbo]

MCaves the post is completly outdated, because it was written in vm2 times. VM3 is a lot faster, also with 100k products.

Normalization is what they teach kids to be the holy grail of database design structure from day 2. It has proven ""concepts"" to a degree but your teacher was wrong to describe this as be all end all LAW

He ignored my already given answer, because I wrote

... a database design is always a compromise between
- Normalisation
- Data reduncy (influences also used space on the hdd)
- Selection performance

The more a database is normalised, the more tables you get. The less data redundancy you have, the more the selection performance goes down. You do not have multiple manufacturers per category, so you do not need this normalisation, you also dont want to search for manufacturers, then remove it.

So I said already, that we dont do database normalisation for itself. A good example are the product prices. They dont have multiple shoppergroups, you would need to store an extra price for each shoppergroup. We get redundant values, because the price rows are the same except for the shoppergroup entry, but it is a lot faster than to add an extra JOIN just for the shoppergroup depended prices. The amount of redundant data is not that much and the feature is used by approximatly less than 20% of our users.