CODEWITHSUNDEEP
phpmysqlleft-joinjoin
user3973556
user3973556

Reputation:

Reduce time of MySQL JOIN statement execution

I have a notifcation system like in Facebook using the following MySQL statement:

SELECT  
n.`id`,n.`content_id`,n.`site_id`,n.`creator_uid`,n.`type`,
nu.`id` AS nuid, nu.`uid` AS nu_uid, nu.`date`,
nr.`id` AS nrid, nr.`uid` AS nr_uid, nr.`is_read`,
u.`gender`

FROM `notification` AS n
LEFT JOIN `notification_user` AS nu ON nu.`nid` = n.`id`
LEFT JOIN `notification_read` AS nr ON nr.`nid` = n.`id` 
LEFT JOIN `users` AS u ON u.`id` = nu.`uid` 
WHERE 
    nu.`uid` != '".$_SESSION['uid']."' AND nr.`uid` = '".$_SESSION['uid']."'
    OR
    (
    nu.`uid` = '".$_SESSION['uid']."' AND n.`type` = 'credits'
    )
ORDER BY date DESC, nu.`id` DESC

It should only display the notifications for this specific user I'm logged in as. But now I've more than 22500 records on the notification table and I'm always getting an "maximum execution time exceeded" error.

Can I change this query somehow to reduce the time getting the wanted records? Maybe remove the join and execute more queries?

EDIT: Added Table Overview

CREATE TABLE IF NOT EXISTS `notification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `creator_uid` int(11) NOT NULL,
  `type` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22759 ;

.

CREATE TABLE IF NOT EXISTS `notification_read` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `is_read` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `nid` (`nid`),
  KEY `nid_2` (`nid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45342 ;

.

CREATE TABLE IF NOT EXISTS `notification_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22813 ;

Upvotes: 2

Views: 151

Answers (1)

Kickstart
Kickstart

Reputation: 21523

Splitting the statement up in to a pair of SELECTs, and UNIONing the results together:-

(SELECT  
n.`id`,n.`content_id`,n.`site_id`,n.`creator_uid`,n.`type`,
nu.`id` AS nuid, nu.`uid` AS nu_uid, nu.`date`,
nr.`id` AS nrid, nr.`uid` AS nr_uid, nr.`is_read`,
u.`gender`
FROM `notification` AS n
INNER JOIN `notification_user` AS nu ON nu.`nid` = n.`id`
LEFT JOIN `notification_read` AS nr ON nr.`nid` = n.`id` 
LEFT JOIN `users` AS u ON u.`id` = nu.`uid` 
WHERE nu.`uid` = '".$_SESSION['uid']."' AND n.`type` = 'credits')
UNION
(SELECT  
n.`id`,n.`content_id`,n.`site_id`,n.`creator_uid`,n.`type`,
nu.`id` AS nuid, nu.`uid` AS nu_uid, nu.`date`,
nr.`id` AS nrid, nr.`uid` AS nr_uid, nr.`is_read`,
u.`gender`
FROM `notification` AS n
LEFT JOIN `notification_user` AS nu ON nu.`nid` = n.`id`
INNER JOIN `notification_read` AS nr ON nr.`nid` = n.`id` 
LEFT JOIN `users` AS u ON u.`id` = nu.`uid` 
WHERE nu.`uid` != '".$_SESSION['uid']."' AND nr.`uid` = '".$_SESSION['uid']."')
ORDER BY date DESC, nu.`id` DESC

This should allow MySQL to use indexes effectively on each part of the query. The first part of the query requires a notification_user record so you can use an INNER JOIN there, while the 2nd requires a notification_read record so you can use an INNER JOIN there. Both of those should cut the number of rows to process.

Add an index on the uid field on the notification_user table

Add an index on the uid field on the notification_read table

Upvotes: 1

Related Questions