I apologize in advance for being so naive about databases, but I'm trying to design a database where I can search five seperate product lists (i.e. from five stores) at the same time. What I have so far is the following:

Database: Stores

CREATE TABLE store1 (
id int(7) NOT NULL auto_increment,
product varchar(100) NOT NULL,
category varchar(150) NOT NULL,
price decimal(7,2) DEFAULT '0000.00' NOT NULL,
shipping decimal(5,2) DEFAULT '00.00' NOT NULL,
availability varchar(30) NOT NULL,
description text NOT NULL,
PRIMARY KEY (ID)
);

Each store table will have thousands of products in it and although I can now search with perl each store in turn for a particular product and output five seperate prices, it is beginning to get very slow (10 seconds). So my question really is, what is the most efficient design and the best way to speed things up other than having an index.

Is it best to keep each store seperate as this will be easier when I update the prices by performing a LOAD DATA INFILE, or should there be multiple tables by means of Normalization.

Any help would be gratefull.

Many thanks.

create a table where you will put the the 5 stores.
You will then need just one table to hold all products with the above structure.
just add a field (storeID) linking the two tables.
that way you will only have to search one table and even filter the stores you wish to search