GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

slow perfomance with multiple LEFT JOINs

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • slow perfomance with multiple LEFT JOINs

    I have a query that is very slow and I am wondering how to improve the speed; here are the details:
    The slow query (table description can be found at the end of the post):
    SELECT * FROM users u LEFT JOIN (user_bookmark ub LEFT JOIN review r ON r.site=ub.bookmark) ON ub.userid=u.userid
    The EXPLAIN of this query tells me
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE u ALL NULL NULL NULL NULL 3000
    1 SIMPLE ub ALL NULL NULL NULL NULL 31220
    1 SIMPLE r ref site site 128 db1.ub.bookmark 2
    and what I find strange is that for ub no index is used (although as you can see from the definition, all fields are indexed).

    The goal of the query is simply to create an overview table of users with all their bookmarks (if they have marked any) and the accompanying site reviews (if any are available).

    If I tryout a first step of the enrichment ie create a table of users extended with their bookmarks I get a very fast query:
    SELECT * FROM users u LEFT JOIN user_bookmark ub ON ub.userid=u.userid

    Does this mean that the "serial" LEFT JOINs kill the efficiency? Is there a way to solve this problem?


    Table definitions

    CREATE TABLE `users` (
    `userid` int(11) NOT NULL auto_increment,
    `name` varchar(100) NOT NULL default '',
    PRIMARY KEY (`userid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3001 ;


    CREATE TABLE `user_bookmark` (
    `bookmark` varchar(128) NOT NULL default '',
    `userid` int(11) NOT NULL default '0',
    KEY `bookmark` (`bookmark`),
    KEY `userid` (`userid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


    CREATE TABLE `review` (
    `site` varchar(128) NOT NULL default '',
    `review` text NOT NULL,
    KEY `site` (`site`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Working...
X