Hello everybody,
I'm looking for some help ! I need to really optimize this query.
The table are designed like this :
CREATE TABLE `comments` (
`id` int(13) NOT NULL auto_increment,
`imgid` int(13) unsigned NOT NULL default '0',
`userid` int(13) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`txt` text NOT NULL,
`edit_uid` int(13) unsigned default NULL,
`edit_date` datetime default NULL,
`deleted` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `imgid` (`imgid`),
KEY `userid` (`userid`),
KEY `date` (`date`),
KEY `deleted` (`deleted`)
) ENGINE=MyISAM AUTO_INCREMENT=162669 DEFAULT CHARSET=latin1;
#
# Table structure for table flags
#
CREATE TABLE `flags` (
`userid` int(13) unsigned NOT NULL default '0',
`imgid` int(13) unsigned NOT NULL default '0',
`lastview` datetime NOT NULL default '0000-00-00 00:00:00',
`views` int(10) unsigned NOT NULL default '1',
`favori` tinyint(1) unsigned NOT NULL default '0',
`suivi` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`userid`,`imgid`,`lastview`),
KEY `favoris` (`imgid`,`favori`),
KEY `suivi` (`suivi`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
#
# Table structure for table img
#
CREATE TABLE `img` (
`NoOrdre` int(13) unsigned NOT NULL default '0',
`id` int(13) unsigned NOT NULL auto_increment,
`url` varchar(250) NOT NULL default '',
`cat` int(3) unsigned NOT NULL default '1',
`hits` int(11) unsigned NOT NULL default '0',
`userid` int(13) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`actif` enum('0','1','2') NOT NULL default '1',
`deleted` tinyint(1) NOT NULL default '0',
`note` float NOT NULL default '-1',
PRIMARY KEY (`id`),
KEY `actif` (`actif`),
KEY `NoOrdre` (`NoOrdre`),
KEY `cat` (`cat`),
KEY `userid` (`userid`),
KEY `deleted_date` (`deleted`,`date`),
KEY `cat_deleted_id` (`cat`,`deleted`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=34945 DEFAULT CHARSET=latin1 PACK_KEYS=0;
#
# Table structure for table users
#
CREATE TABLE `users` (
`id` int(13) unsigned NOT NULL auto_increment,
`email` varchar(100) NOT NULL default '',
`login` varchar(100) NOT NULL default '',
`password` varchar(100) NOT NULL default '',
`dateinscrip` date NOT NULL default '0000-00-00',
`views` int(10) unsigned NOT NULL default '0',
`actif` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `email_index` (`email`),
UNIQUE KEY `login_index` (`login`),
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3950 DEFAULT CHARSET=latin1;
And my request is like that :
SELECT SQL_CALC_FOUND_ROWS i.*, DATE_FORMAT(i.date, '%d-%m-%y') as date, u.login
FROM img i
LEFT JOIN comments c ON c.imgid=i.id
LEFT JOIN users u ON u.id=i.userid
LEFT JOIN flags f ON i.id=f.imgid
WHERE i.deleted=0
AND c.date > f.lastview
AND c.userid!=530
AND f.userid=530
GROUP BY i.id ORDER BY i.id DESC LIMIT 0,12
But it takes too much time (about 5+ sec) and Explain told me that :
+----+-------------+-------+-------+-----------------------------+---------+---------+-------------------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------+---------+---------+-------------------------+-------+-----------------------------+
| 1 | SIMPLE | i | index | PRIMARY,userid,deleted_date | PRIMARY | 4 | NULL | 21493 | Using where; Using filesort |
| 1 | SIMPLE | f | ref | PRIMARY,favoris | PRIMARY | 8 | const,imgalacondev.i.id | 1 | Using index |
| 1 | SIMPLE | u | ref | PRIMARY,id | id | 4 | imgalacondev.i.userid | 1 | |
| 1 | SIMPLE | c | ref | imgid,userid,date | imgid | 4 | imgalacondev.f.imgid | 6 | Using where |
+----+-------------+-------+-------+-----------------------------+---------+---------+-------------------------+-------+-----------------------------+
4 rows in set (0.00 sec)
If you have any ideas, it would be great and very usefull !
Thanks in advance,
Jean-Baptiste,
Edited 1 time(s). Last edit at 07/30/2007 01:44PM by Jean-Baptiste ANNE.