This was actually a reply to another thread ( http://www.magentocommerce.com/boards/viewthread/15581/ ), but I wanted to make a new topic so that maybe a magento developer could weigh in on this.
I am new to this product, using the latest v1.2.1, and have been slowly loading in products over the last few days. First off I don’t want to sound like I’m bashing the product because overall I really like it and wouldn’t be using it if I didn’t. I currently have about 120 products loaded in and the admin section of the site is horribly slow. The frontend is also slow, though thankfully not as slow as the admin.
I know that the site is hosted at godaddy which according to other posts here seems like a bad idea. (I’m looking into other options)
Back to the original reason for my post here. I haven’t tried to debug any of the code, but just looking at the previous post with the query that is being run to retrieve customers I can see why things are so slow. That is an insane query to be trying to run. If any of the developers are seeing this reply, I would really love to know why they would try to run something like that. I can only imagine that the product retrieval queries are similar.
Mysql doesn’t handle joins well to begin with. You shouldn’t do more than 3 or 4 joins at a time if you are trying to have any reasonable performance. If you are going to have 11 joins in a query why wouldn’t you just break that up into mutiple queries. Even with a perfect indexing solution, once the data reached a critical point this query would break down just because of the amount of data that it would have to scan through. Having to wait 9 seconds with only 46,000 customers in the database is completely unacceptable.
In other posts on slow performance I have read that increasing the php memory to 128M or more should help with performance. Again, developers if you are out there, why would this product need to burn through so much memory? With what is happening and the amount of information that needs to be retrieved per page, this shouldn’t need more than about 16M of memory per run and that’s factoring in the memory holding all the includes.
Unless one is expecting to carry a product line in the thousands and millions of customers, magento shouldn’t require a special tricked out set of servers to run. Someone needs to look into the how and why of some of these crazy queries as well as the php memory usage.