Tabele:
CREATE TABLE IF NOT EXISTS `Wpisy` (
`Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Users_Login` varchar(32) NOT NULL,
`Desc` text,
`CreateDate` datetime DEFAULT '0000-00-00 00:00:00',
`ModifyDate` datetime DEFAULT '0000-00-00 00:00:00',
`Active` tinyint(3) unsigned DEFAULT NULL,
`Title` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `Users_Login` (`Users_Login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
CREATE TABLE IF NOT EXISTS `Komentarze` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Wpisy_Id` int(11) NOT NULL DEFAULT '0',
`Users_Login` varchar(32) NOT NULL DEFAULT '',
`dodal` varchar(40) NOT NULL DEFAULT '',
`tytul` varchar(100) NOT NULL DEFAULT '',
`tresc` text NOT NULL,
`createdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`active` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`),
KEY `Wpisy_Id` (`Wpisy_Id`),
KEY `Users_Login` (`Users_Login`),
KEY `createdate` (`createdate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
CREATE TABLE IF NOT EXISTS `Users` (
`Login` varchar(32) NOT NULL DEFAULT '',
`Password` varchar(32) DEFAULT NULL,
`Email` text,
`Name` varchar(150) DEFAULT NULL,
`LastName` varchar(150) DEFAULT NULL,
`City` varchar(150) DEFAULT NULL,
`CreateDate` datetime DEFAULT NULL,
`LastLoginDate` datetime DEFAULT NULL,
`LastLoginIp` varchar(15) DEFAULT NULL,
`LastLoginBrowser` varchar(35) DEFAULT NULL,
`LastLoginSystem` varchar(35) DEFAULT NULL,
`Active` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`Login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
Zapytanie:
SELECT *
FROM Komentarze
LEFT JOIN Wpisy ON Wpisy.id = Komentarze.Wpisy_Id
JOIN Users ON Users.Login = Komentarze.Users_Login
WHERE Komentarze.active =1
AND Komentarze.dodal = "nazwa"
ORDER BY Komentarze.createdate DESC
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE komentarz ALL NULL NULL NULL NULL 154604 100.00 Using where; Using filesort
1 SIMPLE Users eq_ref PRIMARY PRIMARY 34 test.Komentarze.Users_Login 1 100.00
1 SIMPLE Item eq_ref PRIMARY PRIMARY 4 test.Komentarze.Wpisy_Id 1 100.00