Posto ne moze sve da stane u jednu poruku evo u 3 dela
Code:
mysql Ver 14.14 Distrib 5.1.62, for pc-linux-gnu (x86_64) using readline 5.1
explain extended select k.uid from keywords k join groups g on g.uid = k.groupid join category_kwgroups ck using(category_kwgroupid) join category_kwgroupkw ckk using(category_kwgroupid) where g.codice_cat = 42050;
+----+-------------+-------+--------+----------------------+---------+---------+------------------------------+----------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+----------------------+---------+---------+------------------------------+----------+----------+-------------+
| 1 | SIMPLE | k | ALL | groupid | NULL | NULL | NULL | 44774871 | 100.00 | |
| 1 | SIMPLE | g | eq_ref | PRIMARY,g_codice_cat | PRIMARY | 8 | pgclick.k.groupid | 1 | 100.00 | Using where |
| 1 | SIMPLE | ckk | ref | PRIMARY | PRIMARY | 8 | pgclick.g.category_kwgroupid | 3 | 100.00 | Using index |
| 1 | SIMPLE | ck | eq_ref | PRIMARY | PRIMARY | 8 | pgclick.g.category_kwgroupid | 1 | 100.00 | Using index |
+----+-------------+-------+--------+----------------------+---------+---------+------------------------------+----------+----------+-------------+
explain extended select ckk.* from keywords k join groups g on g.uid = k.groupid join category_kwgroups ck using(category_kwgroupid) join category_kwgroupkw ckk using(category_kwgroupid) where g.codice_cat = 42050;
+----+-------------+-------+--------+----------------------+---------+---------+------------------------------+--------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+----------------------+---------+---------+------------------------------+--------+----------+-------------+
| 1 | SIMPLE | g | ALL | PRIMARY,g_codice_cat | NULL | NULL | NULL | 380553 | 100.00 | Using where |
| 1 | SIMPLE | ckk | ref | PRIMARY | PRIMARY | 8 | pgclick.g.category_kwgroupid | 3 | 100.00 | |
| 1 | SIMPLE | ck | eq_ref | PRIMARY | PRIMARY | 8 | pgclick.g.category_kwgroupid | 1 | 100.00 | Using index |
| 1 | SIMPLE | k | ref | groupid | groupid | 8 | pgclick.g.uid | 231 | 100.19 | Using index |
+----+-------------+-------+--------+----------------------+---------+---------+------------------------------+--------+----------+-------------+
CREATE TABLE `keywords` (
`clientid` bigint(20) unsigned NOT NULL DEFAULT '0',
`groupid` bigint(20) NOT NULL DEFAULT '0',
`uid` bigint(20) NOT NULL AUTO_INCREMENT,
`text` varchar(80) NOT NULL DEFAULT '',
`status` enum('Pending','Active','Deleted','ToBeDeleted','ToBeActivated','Not Active') NOT NULL DEFAULT 'Active',
`type` enum('Positive','Negative') DEFAULT 'Positive',
`matchType` enum('Broad+','Broad','Exact','Phrase') NOT NULL DEFAULT 'Broad',
`created` timestamp NULL DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`advertiserCompetitionScale` smallint(6) NOT NULL DEFAULT '0',
`searchVolumeScale` smallint(6) NOT NULL DEFAULT '0',
`checkedperfomance` timestamp NULL DEFAULT NULL,
`insertedBy` char(64) NOT NULL DEFAULT 'Source 1',
`blocked` tinyint(1) NOT NULL DEFAULT '0',
`kwdid` bigint(200) NOT NULL DEFAULT '0',
`category_type` varchar(20) NOT NULL DEFAULT 'CATEGORY',
PRIMARY KEY (`uid`),
KEY `clientid` (`clientid`),
KEY `groupid` (`groupid`),
KEY `status` (`status`),
KEY `competition` (`advertiserCompetitionScale`,`searchVolumeScale`,`checkedperfomance`),
KEY `text` (`text`),
KEY `keywords_kwdid` (`kwdid`)
) ENGINE=MyISAM AUTO_INCREMENT=483131359 DEFAULT CHARSET=utf8
CREATE TABLE `groups` (
`clientid` bigint(20) unsigned NOT NULL DEFAULT '0',
`uid` bigint(20) NOT NULL AUTO_INCREMENT,
`idprovincia` int(11) NOT NULL DEFAULT '0',
`codice_cat` varchar(20) NOT NULL DEFAULT '',
`category_kwgroupid` bigint(20) DEFAULT NULL,
`name` char(255) NOT NULL DEFAULT '',
`status` enum('Pending','Active','Paused','Deleted') NOT NULL DEFAULT 'Active',
`created` timestamp NULL DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`campaignid` bigint(20) NOT NULL DEFAULT '0',
`type` char(20) NOT NULL DEFAULT 'CATEGORY',
PRIMARY KEY (`uid`),
KEY `clientid` (`clientid`),
KEY `campaignid` (`campaignid`),
KEY `g_codice_cat` (`codice_cat`)
) ENGINE=MyISAM AUTO_INCREMENT=505480 DEFAULT CHARSET=utf8
CREATE TABLE `category_kwgroups` (
`categoryid` int(11) NOT NULL,
`category_kwgroupid` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
`maxCPC` decimal(17,2) DEFAULT '0.00',
`language` char(2) NOT NULL DEFAULT 'IT',
`created` timestamp NULL DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`category_kwgroupid`)
) ENGINE=MyISAM AUTO_INCREMENT=71 DEFAULT CHARSET=utf8
CREATE TABLE `category_kwgroupkw` (
`category_kwgroupid` bigint(20) NOT NULL,
`categorykwid` bigint(20) NOT NULL,
`status` varchar(32) NOT NULL DEFAULT 'Active',
`type` enum('Positive','Negative') NOT NULL DEFAULT 'Positive',
`matchType` enum('Broad','Exact','Phrase') NOT NULL DEFAULT 'Broad',
`blocked` tinyint(1) NOT NULL DEFAULT '0',
`created` timestamp NULL DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`category_kwgroupid`,`categorykwid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
