How to Maintain and Optimize SQL Queries with Large Tables?

[ozzii]

Hi, I have a table called products which consist of various supplier
products and has the following fields:

ID int not null primary key auto_increment
SupplierID int not null
ProductCode varchar(30) not null
productName varchar(255) null
Length varchar(20) null
Width varchar(20) null
Height varchar(20) null
Weight varchar(20) null


The table will be frequently searched online using the following fields using the LIKE where clause: productCode, ProductName, length, width, height, weight

The table will frequently be updated with supplier products.

Up to 300 suppliers are anticipated.

Each supplier may have a product catalogue consisiting of 5000 to 10,000 products.

A supplier can update their catalogue by adding, deleting products to the above table or delete the entire catalogue and insert a new catalogue in which case all the products supplied by the supplier will be deleted (This could be 10,000 records) and then insert a different set of 10,000 products.

As you can see the table will be very large approx 1 million records and needs to be optimized for fast SQL queries whilst maintainig a balance to compensate for the frequent updates i.e large table inserts and deletes.

Now my questions to acheive the above functionality:

1. Which fields should i Index?

2. if I Index the fields on which the SQL query will take place
how long will it take mySQL to re-index those fields with the frequent updates that I require?

3. Is there a more efficient way to achieve the above functionality?

4. Should I be aware of other issues for maintianing this table and running the SQL search query?

Your help and advice will be much appreciated

[ronverdonk]

This is a duplicate thread of http://www.thescripts.com/forum/thread605775.html , which was also posted by you. Since this thread is more detailed, I will remove the older thread from this forum.

moderator