[Linuus]

Jag kopierar in mitt inlägg från SO här så kanske någon här kan hjälpa mig med detta.

EXPLAIN på queryn som beskrivs nedan ger detta:
http://img268.imageshack.us/img268/3...10309kl015.png

Vilket tyder på att problemet har med catraws-tabellen att göra. Den gör ju en full table scan på den verkar det som och måste skriva en tmp-tabell till disken då.

Hi!

I have a database that stores products. The two biggest tables are my products table and images table.
Products: ~19.000 rows

Images: ~34.000 rows

Categories: ~60 rows

Raw categories: ~1200 rows

Brands: 700 rows


(The other tables are only a couple of rows)



I also have a table with brands, raw categories, static categories (the raw categories are mapped to a static category).

I have created a Product VIEW to gather the data I need, like this:

    CREATE OR REPLACE VIEW `jos_clothes_view_products` AS
    SELECT tbl.*, r.name AS reseller, b.name AS brand, rcat.raw_name AS cats_raw, cats1.name AS cat1, cats1.slug AS catslug1, cats2.name AS cat2, cats2.slug AS catslug2
    FROM `jos_clothes_products` AS tbl
    LEFT JOIN `jos_clothes_brands` AS b ON b.clothes_brand_id = tbl.clothes_brand_id
    LEFT JOIN `jos_clothes_resellers` AS r ON r.clothes_reseller_id = tbl.clothes_reseller_id
    LEFT JOIN `jos_clothes_catraws` AS rcat ON rcat.clothes_catraw_id = tbl.clothes_catraw_id
    LEFT JOIN `jos_clothes_categories` AS cats2 ON cats2.clothes_category_id = rcat.clothes_category_id
    LEFT JOIN `jos_clothes_categories` AS cats1 ON cats1.clothes_category_id = cats2.parent_id

Then when running a query like this from PHP:

SELECT `tbl`.* FROM `jos_clothes_view_products` AS `tbl` WHERE `tbl`.`cat1` != 'NULL' AND `tbl`.`enabled` = '1' ORDER BY `created_on` DESC , `ordering` ASC LIMIT 0 , 20;

The query is often very slow! Not always though (probably because of caching?). I have also noticed that it creates a tmp-table at about 200MB in size. Sometimes it gets even bigger and fails with "Invalid key for file....".


Any ideas how I can optimize the query? Or the VIEW actually, I guess it's the bottleneck here. Correct?






Product table structure:

    CREATE TABLE IF NOT EXISTS `jos_clothes_products` (
      `clothes_product_id` int(11) unsigned NOT NULL auto_increment,
      `clothes_reseller_id` int(11) unsigned NOT NULL,
      `aff_prod_id` varchar(50) NOT NULL,
      `title` varchar(255) NOT NULL,
      `description` text NOT NULL,
      `gender` varchar(20) NOT NULL,
      `clothes_brand_id` int(11) unsigned NOT NULL,
      `color` varchar(255) NOT NULL,
      `size` varchar(50) NOT NULL,
      `clothes_catraw_id` bigint(20) unsigned NOT NULL,
      `price` decimal(10,2) NOT NULL default '0.00',
      `shipping_cost` varchar(20) NOT NULL default '0.00',
      `currency` varchar(10) NOT NULL,
      `availibility` tinyint(1) NOT NULL,
      `product_url` varchar(350) NOT NULL,
      `real_url` varchar(300) NOT NULL,
      `slug` varchar(255) NOT NULL,
      `hits` int(11) NOT NULL,
      `enabled` tinyint(1) NOT NULL default '0',
      `access` int(11) NOT NULL default '0',
      `ordering` bigint(20) unsigned NOT NULL,
      `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
      `created_by` int(11) NOT NULL default '0',
      `modified_on` datetime NOT NULL default '0000-00-00 00:00:00',
      `modified_by` int(11) NOT NULL default '0',
      `locked_on` datetime NOT NULL default '0000-00-00 00:00:00',
      `locked_by` int(11) NOT NULL default '0',
      PRIMARY KEY  (`clothes_product_id`),
      KEY `clothes_brand_id` (`clothes_brand_id`),
      KEY `clothes_catraw_id` (`clothes_catraw_id`),
      KEY `created_on` (`created_on`),
      KEY `clothes_reseller_id` (`clothes_reseller_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18962 ;

Any ideas?

Best Regards




EDIT: Structure of jos_clothes_catraws

    --
    -- Struktur för tabell `jos_clothes_catraws`
    --
    
    CREATE TABLE IF NOT EXISTS `jos_clothes_catraws` (
      `clothes_catraw_id` int(11) unsigned NOT NULL auto_increment,
      `clothes_category_id` int(11) unsigned NOT NULL default '0',
      `clothes_reseller_id` int(11) unsigned NOT NULL,
      `raw_name` varchar(255) NOT NULL,
      PRIMARY KEY  (`clothes_catraw_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1289 ;