Results 1 to 11 of 11

Thread: query help

  1. #1
    Join Date
    Mar 2010
    Posts
    6

    Unanswered: query help

    Hello, my mysql knowledge is limited and after doing some reading, tests I am unable to find any solution and would appreciate any help. The task is to find products that with specific tags.

    The main products table

    CREATE TABLE IF NOT EXISTS `ds_item` (
    `iid` int(11) NOT NULL auto_increment,
    `status` int(5) NOT NULL default '0',
    `title` varchar(255) NOT NULL default '',
    `date` int(11) NOT NULL default '0',
    `dupdate` int(11) NOT NULL default '0',
    `sale` smallint(2) NOT NULL default '0',
    `price_list` varchar(50) NOT NULL default '0.00',
    `price_sale` varchar(50) NOT NULL default '0.00',
    `price_sale_avg` decimal(15,2) NOT NULL default '0.00',
    `price_save` decimal(15,2) NOT NULL default '0.00',
    `store_id` int(11) NOT NULL default '0',
    `pid` varchar(110) NOT NULL,
    `sku` varchar(100) NOT NULL,
    `upc` varchar(100) NOT NULL,
    `url` text NOT NULL,
    `description` text NOT NULL,
    PRIMARY KEY (`iid`),
    KEY `store_id` (`store_id`),
    KEY `status` (`status`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2124683 ;


    tags' table

    CREATE TABLE IF NOT EXISTS `ds_item_cat` (
    `rel_id` int(11) NOT NULL auto_increment,
    `iid` int(11) NOT NULL default '0',
    `cat` int(11) NOT NULL default '0',
    PRIMARY KEY (`rel_id`),
    KEY `iid_cat` (`iid`,`cat`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6127161 ;


    stores' table

    CREATE TABLE IF NOT EXISTS `ds_store` (
    `store_id` int(11) NOT NULL auto_increment,
    `store_name` varchar(255) NOT NULL default '',
    `store_status` smallint(3) NOT NULL default '0',
    `currency` char(3) NOT NULL default 'usd',
    `note` varchar(255) NOT NULL,
    PRIMARY KEY (`store_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=539 ;


    now a sample query to get Women's Black Steve Madden Sandals

    SELECT ds_item.iid, title, sale, price_list, price_sale, price_sale_avg, ds_item.store_id, description, store_name, currency FROM ds_item
    INNER JOIN ds_item_cat AS c1 ON ds_item.iid=c1.iid
    INNER JOIN ds_item_cat AS gender ON ds_item.iid=gender.iid AND gender.cat='4118'
    INNER JOIN ds_item_cat AS brand ON ds_item.iid=brand.iid AND brand.cat='715'
    INNER JOIN ds_item_cat AS color ON ds_item.iid=color.iid AND color.cat='4127'
    LEFT JOIN ds_store ON (ds_item.store_id=ds_store.store_id)
    WHERE status='0' AND c1.cat IN ('4160')
    GROUP BY ds_item.iid
    ORDER BY sale DESC, ds_item.dupdate DESC
    LIMIT 0, 10

    The problem: when I run explain on this query it shows that about 93947 rows need to be scanned. Any way to avoid this by optimizing the query, tables? Thank you.


    the results of explain

    http://img218.imageshack.us/img218/8274/explain.gif

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how many rows have status 0 versus other status values?

    why are you using GROUP BY if there is no aggregation (SUM or COUNT) involved?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    6
    Thank you r937, you are right, the group by needs to be removed, will do it now.

    159,519 rows have status 0 (0=active/in stock items)
    318,117 rows are status 1 (out of stock items)
    the rest are status 2 (soft deleted)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and did the EXPLAIN improve without the GROUP BY?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    6
    no looks the same, 94007 rows are being scanned without the group by.


    here are the results of the explain without the group by:

    http://img34.imageshack.us/img34/7846/explainm.gif

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by virap View Post
    no looks the same, 94007 rows are being scanned without the group by.
    not using temporary any more, though, is it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2010
    Posts
    6
    You are correct, sorry I completely missed that. That is a big improvement then, thank you.

    Overall, do I have everything now correct? Is it normal for the query to scan so many rows just to return few?
    Last edited by virap; 03-03-10 at 17:35.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by virap View Post
    Is it normal for the query to scan so many rows just to return few?
    ask yourself where the 10 biggest sale items are likely to be located within all hunnert thousand instock items
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2010
    Posts
    6
    do you mean "ORDER BY sale DESC" is causing all these rows to be scanned? If so I tried it without sort too, but it keeps scanning 94000+ rows.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by virap View Post
    If so I tried it without sort too, but it keeps scanning 94000+ rows.
    well, the omission of the ORDER BY clause would make your query results hardly useful at all, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2010
    Posts
    6
    Yes, but it still scans 94K rows either way, with our without the order part.

Posting Permissions

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