CODEWITHSUNDEEP
phpmysqlsqlcodeigniterjoin
stergosz
stergosz

Reputation: 5860

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

Upvotes: 3

Views: 2407

Answers (5)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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.

Upvotes: 2

user1027167
user1027167

Reputation: 4448

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.

Upvotes: 1

Luc
Luc

Reputation: 985

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.

Upvotes: 0

user359040
user359040

Reputation:

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.

Upvotes: 1

Brian
Brian

Reputation: 6450

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)

Upvotes: 0

Related Questions