# slow perfomance with multiple LEFT JOINs

**URL:** <https://forums.percona.com/t/slow-perfomance-with-multiple-left-joins/816>\
**Category:** Other MySQL® Questions\
**Created:** [June 27, 2008, 5:04am UTC](https://forums.percona.com/t/slow-perfomance-with-multiple-left-joins/816 "2008-06-27T05:04:35Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![phlype](https://avatars.discourse-cdn.com/v4/letter/p/6f9a4e/32.png) [@phlype](https://forums.percona.com/u/phlype)\
**Post date:** [June 27, 2008, 5:04am UTC](https://forums.percona.com/t/slow-perfomance-with-multiple-left-joins/816/1 "2008-06-27T05:04:35Z")

</div>

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;