mysql left join takes too long

mysql left join takes too long

I have the following SQL Query:

SELECT       upd.*,      usr.username AS `username`,      usr.profile_picture AS `profile_picture`  FROM       updates AS upd  LEFT JOIN       subscribers AS sub ON upd.uid=sub.suid  LEFT JOIN       users AS usr ON upd.uid=usr.uid  WHERE       upd.deleted='0' && (upd.uid='118697835834' || sub.uid='118697835834')  GROUP BY upd.id  ORDER BY upd.date DESC  LIMIT 0, 15  

where i get all user(118697835834) updates, his profile picture from another table using left join and also all his subscription users updates so can i show them in his newsfeed.

However as the updates get more and more so the query takes more time to load... right now using Codeigniter's Profiler i can see that the query takes 1.3793...

Right now i have created around 18k dummy accounts and subscribed from to me and vice versa so i can test the execution time... the times that i get are tragic considering that i am in localhost...

I also have some indexes where i suppose need more in the users table(username and uid as unique), updates table(update_id as unique and uid as index)

I suppose i am doing something wrong to get so bad results...

EDIT: Running EXPLAIN EXTENDED result:

Array  (      [0] => stdClass Object          (              [id] => 1              [select_type] => SIMPLE              [table] => upd              [type] => ALL              [possible_keys] => i2              [key] =>               [key_len] =>               [ref] =>               [rows] => 22              [filtered] => 100.00              [Extra] => Using where; Using temporary; Using filesort          )        [1] => stdClass Object          (              [id] => 1              [select_type] => SIMPLE              [table] => sub              [type] => ALL              [possible_keys] =>               [key] =>               [key_len] =>               [ref] =>               [rows] => 18244              [filtered] => 100.00              [Extra] => Using where          )        [2] => stdClass Object          (              [id] => 1              [select_type] => SIMPLE              [table] => usr              [type] => eq_ref              [possible_keys] => uid              [key] => uid              [key_len] => 8              [ref] => site.upd.uid              [rows] => 1              [filtered] => 100.00              [Extra] =>           )    )  

EDIT2: SHOW CREATE of Tables Users table:

CREATE TABLE `users` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `uid` bigint(20) NOT NULL,   `username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,   `email` text CHARACTER SET latin1 NOT NULL,   `password` text CHARACTER SET latin1 NOT NULL,   `profile_picture_full` text COLLATE utf8_unicode_ci NOT NULL,   `profile_picture` text COLLATE utf8_unicode_ci NOT NULL,   `date_registered` datetime NOT NULL,   `activated` tinyint(1) NOT NULL,   `closed` tinyint(1) NOT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `uid` (`uid`),   UNIQUE KEY `username` (`username`)  ) ENGINE=MyISAM AUTO_INCREMENT=23521 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci  

Subscribers table:

CREATE TABLE `subscribers` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `sid` bigint(20) NOT NULL,   `uid` bigint(20) NOT NULL,   `suid` bigint(20) NOT NULL,   `date` datetime NOT NULL,   PRIMARY KEY (`id`)  ) ENGINE=MyISAM AUTO_INCREMENT=18255 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci  

Updates table:

CREATE TABLE `updates` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `update_id` bigint(19) NOT NULL,   `uid` bigint(20) NOT NULL,   `type` text COLLATE utf8_unicode_ci NOT NULL,   `update` text COLLATE utf8_unicode_ci NOT NULL,   `date` datetime NOT NULL,   `total_likes` int(11) NOT NULL,   `total_comments` int(11) NOT NULL,   `total_favorites` int(11) NOT NULL,   `category` bigint(20) NOT NULL,   `deleted` tinyint(1) NOT NULL,   `deleted_date` datetime NOT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `i1` (`update_id`),   KEY `i2` (`uid`),   KEY `deleted_index` (`deleted`)  ) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci  

Answer by Brian for mysql left join takes too long

Shouldn't take too long to run; do you have an index on 'deleted'? What is the 'GROUP BY id' doing? Should it be UID? Can it come out, if ID is in fact just an auto increment, unique ID? (which would be expensive as well as pointless)

Answer by Mark Bannister for mysql left join takes too long

Try:

SELECT       upd.*,      usr.username AS `username`,      usr.profile_picture AS `profile_picture`  FROM       updates AS upd  LEFT JOIN       subscribers AS sub ON upd.uid=sub.suid  LEFT JOIN       users AS usr ON upd.uid=usr.uid  WHERE       upd.deleted=0 and upd.uid in (118697835834,118697835834)  GROUP BY upd.id  ORDER BY upd.date DESC  LIMIT 0, 15  

Note that ' has been removed from numeric values and bitwise operators changed to conventional operators.

Answer by Luc for mysql left join takes too long

I think you'll be best separating this query into a select on the user table and then union those results with the select on the subscribers table.

Answer by user1027167 for mysql left join takes too long

don't use joins, try this one:

select  *,           (select username from users where uid = upd.uid) as username,          (select profile_picture from users where uid = upd.uid) as profile_picture,  from    updates as upd  WHERE       upd.deleted='0' && upd.uid='118697835834'  

(not tested!)

maybe you have to check if there exists a subscriber in the where-clause with another sub-select.

Another way would be to make a join on sub-selects and not on the whole table. This may increase your performance also.

Answer by ypercube?? for mysql left join takes too long

Try this one (without the GROUP BY):

SELECT       upd.*,      usr.username AS `username`,      usr.profile_picture AS `profile_picture`  FROM           updates AS upd      LEFT JOIN           users AS usr               ON  upd.uid = usr.uid  WHERE       upd.deleted='0'     AND       ( upd.uid='118697835834'     OR EXISTS        ( SELECT *          FROM   subscribers AS sub           WHERE  upd.uid = sub.suid            AND  sub.uid = '118697835834'        )      )  ORDER BY upd.date DESC  LIMIT 0, 15  

---

At least the columns that are used in Joins should be indexed: updates.uid, users.uid and subscribers.suid.

I would also add an index on subscribers.uid.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72