Maximum number of items in a MySQL Table

Goodday everyone.

I met with a client yesterday that is running an eCommerce website with a highly modified version of OSCommerce (connected to a MySQL Database. They sell parts for small tractors, landscape equipment, etc. Their MySQL Database contains over 100,000 items in it.

Their problem with the existing system is that when a site visitor searches for a part (all searches are by part number because they find that on the drawings on their website AND the table is sorted alpha-numerically by part number) it can literally cripple their server while the system looks up the part to return it to the OSCommerce Shopping Cart.

They are thinking they should break their database up into smaller tables, but I'm thinking that a more efficiently designed search query would probably help them. I'm not sure that can be accomplished with their current OSCommerce system because it is something they bought and I'll bet the developer encrypted the code.

Regardless, I'm trying to find out if something like Market Press could be used to rebuild their website in WP or would they still have problems simply because of the number of items in their MySQL Database. Maybe my search queries have been for the wrong terms, but I've not found anything yet that gives any indication of a limit on number of items in a MySQL Database for most efficient searching.

Any ideas would be appreciated here because it would help me get this project - and this project would be HUGE for me.

Thanks in advance for your thoughts.

JDD

  • aecnu

    Greetings JDD :slight_smile:

    This is a great question that one of my colleagues asked me to take due to the unfamiliarity they have with the internals of MySQL.

    What version of OSCommerce?

    Older versions of OSCommerce did not use "Key" indexing and were dogs on MySQL, but newer versions have overcome this.

    I've not found anything yet that gives any indication of a limit on number of items in a MySQL Database for most efficient searching.

    That is because the performance is tied to the efficiency of the host, programming, and the "Key" index.

    Of course the beef of the server is also in this and you are only as fast as your slowest process.

    They are thinking they should break their database up into smaller tables, but I'm thinking that a more efficiently designed search query would probably help them.

    This would not be very beneficial breaking it into more tables, only more databases and that cannot be done to the best of my knowledge. But a more efficient search query and a keyed table would be of great value.

    In regards to Market press products table, it indeed indicates that it is keyed.

    Please advise as to the version of OSCommerce.

    Cheers my friend, Joe

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.