Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005

    Unanswered: Searching five product lists at the same time

    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,

    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.

  2. #2
    Join Date
    Jun 2005
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts