Large MySQL Products Database Hosting Advice

[stebal4]

Tweet

xxxxxxxxxxxxxxxxxx

[Cheapdvds]

split the products into categories and have a database for each category ?

[IntroSites]

Make sure every query is performed on an index, avoid table scans at all costs

[Cheapdvds]

for example , u have one search bar and a drop down menu at the side for the categories, all the products together are 10 million items , but as u only search the selected category (one of the databases) then u are searching a lot less items .

[Cheapdvds]

Advice on servers | Price Tapestry

may help

[pricespin]

As David @ Price Tapestry has said, sorting will probably be the biggest problem as sorting several thousand rows real time is a little ambitious for a small site.

I would look at using pre-defined views and queries.

BTW. that's a great article on PT forum.

[Colin@DVDTimes]

For the actual hosting provider I strongly recommend Vidahost - by far the best hosting company I've dealt with.

We had a major server issue at our current host yesterday, and Vidahost stepped in and have provided a server free of charge until our new main server is up and running. They did this at 6pm last night and then spent two hours setting it up for me out of hours without charging me a penny.

Best company I've ever dealt with bar none.

[Jack Russell]

I'm building a new site which is going to have a large MySQL products database, few million products, probably 1 to 2GB in total size. Currently I've uploaded 300k products and already the site is starting to slow down on my reseller hosting package.

I'm contemplating moving onto a dedicated server to help speed things up and was wondering if anyone has any advice when it comes to which company to use and who offers the MySQL space I require.

Also, when it comes to building such a large MySQL database, does anyone have any advice on speeding things up through queries (like not using RAND(), one I've noticed so far).

You can consider one of our Dedicated Server from Dedicated Server Hosting | Managed Dedicated Servers | Linux Windows SQL Servers

We'll take care of optimizing your MySQL databases. All our servers are fully managed along with 24x7 support via phone, live chat and help desk.

Let me know if you need any help

[Jack Russell]

There is lot of difference in server specifications we offer. We use only branded Chassis from Dell and latest state of the art Intel CPU’s for our servers. We offer 100% network and power uptime guarantee on all our dedicated servers. You can also check the support modes offered by us and compare.

Moreover, we do have special offers for a4u members where you can save 10% on the pricing.

[aotagain]

First of all you need to establish what is slowing the process down - is it the database size per se and the db/site connection or is it the query time?

I would put 95% of my money on the queries.
As someone said you need to query indexes on the db, rather than the full db content.

Why not do a simple query comparision to test ?
ie. select a couple of specific id's in a query from the db and see what speed you get - that should be lightning quick. Then do a more complex matching 'like' several criteria query and compare.

If its the query, using indexes will help.

However, I've been doing something a bit smaller with around 300,000 products and it was slow even using indexes. Plus I could never get the right results from the queries because the product descriptions and names varied so much between merchants, so the queries were very long and slow when done live.

So in the end, I wrote and recorded the queries for each page in a separate db table.
They are then run on the main table, giving a list of table ids for each page.
When the site page runs it then just queries the main table for the id's which is v. quick.

Only problem is you're not running 'live' queries, but doesn't matter if merchants are not changing prices too much.