Acquiring product details

Question

I have these MySQL tables:

-- Create product table
CREATE TABLE `product` (
  `product_id`       int            NOT NULL auto_increment,
  `price`            decimal(10,2)  NOT NULL default '0.00',
  `discounted_price` decimal(10,2)  NOT NULL default '0.00',
  `avail`            enum('Y','N')  NOT NULL default 'Y',
  `image`            varchar(150)   NOT NULL default '',
  `image_2`          varchar(150)   NOT NULL default '',
  `thumbnail`        varchar(150)   NOT NULL default '',
  `thumbnail_2`      varchar(150)   NOT NULL default '',
  `for_sale`         enum('Y','N')  NOT NULL default 'Y',
  `views_stats`      int            NOT NULL default '0',
  `sales_stats`      int            NOT NULL default '0',
  `amount_stats`     decimal(12,2)  NOT NULL default '0.00',
  PRIMARY KEY  (`product_id`),
  KEY `idx_avail` (`avail`),  
  KEY `idx_for_sale` (`for_sale`)
) ENGINE=MyISAM default charSET=utf8 COLLATE=utf8_unicode_ci;

-- Create table for product languages
CREATE TABLE `product_languages` (
  `language_id_iso_639_1` char(2)       NOT NULL default '',
  `product_id`            int           NOT NULL auto_increment,
  `name`                  varchar(100)  NOT NULL default '',
  `description`           varchar(1000) NOT NULL default '',
  `keywords`              varchar(255)  NOT NULL default '',
  `meta_description`      text          NOT NULL,
  `meta_keywords`         text          NOT NULL,
  `title_tag`             text          NOT NULL,
  PRIMARY KEY  (`language_id_iso_639_1`, `product_id`),
  FULLTEXT KEY `idx_ft_product_name_description` (`name`, `description`, `keywords`)
) ENGINE=MyISAM DEFAULT charSET=utf8 COLLATE=utf8_unicode_ci;

-- Create product_category table 'many to many relationship' between products and categories
    CREATE TABLE `product_category` (
      `product_id`  int NOT NULL default '0',
      `category_id` int NOT NULL default '0',
      PRIMARY KEY (`product_id`, `category_id`)
    ) 
ENGINE=MyISAM default charSET=utf8 COLLATE=utf8_unicode_ci;

Then I try to get all products from table product with the product details in English from table product_languages, and from a specific category looking at product_category table and where avail from table product is Y, something like this:

SELECT 
      product.product_id,
      product.price,
      product.discounted_price,
      product.avail,
      product.image,
      product.image_2,
      product.thumbnail,
      product.thumbnail_2,
      product.for_sale,
      product.views_stats,
      product.sales_stats,
      product.amount_stats,
      product_languages.name, 
      product_languages.description, 
      product_languages.keywords, 
      product_languages.meta_description, 
      product_languages.meta_keywords, 
      product_languages.title_tag
      FROM product
      INNER JOIN product_category
                   ON product.product_id = product_category.product_id
      INNER JOIN product_languages
                   ON product.product_id = product_languages.product_id
      WHERE product_category.category_id = 1
                   AND product.avail = 'Y'
                   AND product_languages.language_id_iso_639_1 = 'en'
      ORDER BY product.product_id
      LIMIT 0 , 30;

It works (I tried it with PHPMyAdmin). I get all products from the product table that belong to a category that are avail='Y', and I get the names and descriptions from every product in English (en) from product_languages table.

Could someone with experience with MySQL suggest any changes? I'm not really sure if what I do is correct. Should I change tables, fields, or types for better options, such as enums, or remove or add better indexes to improve speed?

For example, maybe add another index at product_id from product_languages table because now I have PRIMARY KEY ('language_id_iso_639_1', 'product_id') and I use for the second INNER JOIN ON product.product_id = product_languages.product_id, is it correct the FULLTEXT KEY at product_languages table FULLTEXT KEY 'idx_ft_product_name_description' ('name', 'description', 'keywords') to use that feature to look at those 3 fields, change parts of the query with the INNER JOINS change the order of INNER JOINS first INNER JOIN table product_languages and second INNER JOIN product_category, etc.

Answer 1

I'm not an expert as say, but my only suggestions might be:

1) If the enums are only ever going to be Y, N, you could look at changing these to the bit flag.

2) I would perhaps look at removing the defaults from the fields that are defined as part of the primary key i.e. product_category. This would force the values to be always supplied from the application.

3) product_languages.product_id. I don't understand the auto_increment setting here. The field appears to want to be a foreign_key to the products table but you have set it as a auto_increment field. I would perhaps remove the auto_increment and set an index on it.