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;