Description:
When I execute explain direct on the query, I get diffrent results from the big query with the devired tables.
How to repeat:
CREATE TABLE `articles` (
`a_id` int(11) NOT NULL AUTO_INCREMENT,
`a_title` varchar(90) NOT NULL DEFAULT '',
`a_date` bigint(20) NOT NULL DEFAULT '0',
`a_cat` double(4,0) NOT NULL DEFAULT '0',
`a_short` varchar(400) DEFAULT NULL,
`a_replies` smallint(6) NOT NULL DEFAULT '0',
`a_visible` smallint(1) NOT NULL DEFAULT '0' COMMENT '0 - disabeld; 1- articles; 2 - torah',
`a_author` smallint(6) NOT NULL DEFAULT '0',
`a_authorName` varchar(75) NOT NULL DEFAULT '',
`a_content` mediumtext NOT NULL,
`a_special` int(11) NOT NULL DEFAULT '0',
`a_image` int(11) NOT NULL DEFAULT '0',
`a_upDate` bigint(14) NOT NULL DEFAULT '0',
`a_nlsent` int(1) unsigned NOT NULL DEFAULT '0',
`a_tags` varchar(255) DEFAULT NULL,
`a_largeImage` int(11) unsigned DEFAULT '0',
`a_audio` smallint(1) unsigned zerofill NOT NULL DEFAULT '0',
`a_album` smallint(1) unsigned zerofill NOT NULL DEFAULT '0',
`a_video` smallint(1) unsigned zerofill NOT NULL DEFAULT '0',
PRIMARY KEY (`a_id`),
KEY `Cat` (`a_cat`,`a_date`),
KEY `author` (`a_author`),
KEY `autors` (`a_author`,`a_date`),
KEY `getLast` (`a_visible`,`a_cat`,`a_date`),
KEY `cat-date-id` (`a_cat`,`a_date`)
) ENGINE=MyISAM AUTO_INCREMENT=10029 DEFAULT CHARSET=utf8$$
CREATE TABLE `authors` (
`au_id` int(11) NOT NULL AUTO_INCREMENT,
`au_name` text NOT NULL,
`au_description` text,
`au_isConst` smallint(6) NOT NULL DEFAULT '0',
`au_image` int(11) NOT NULL DEFAULT '0',
`au_lname` varchar(53) NOT NULL DEFAULT '',
`au_url` varchar(105) NOT NULL DEFAULT '',
`au_email` varchar(45) NOT NULL DEFAULT '',
`au_pref` varchar(30) NOT NULL DEFAULT '',
`au_short` varchar(14) NOT NULL,
PRIMARY KEY (`au_id`,`au_isConst`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=872 DEFAULT CHARSET=utf8$$
/* Fill tables .... */
explain select a.*,au_id,au_image from (
select * from (
select a_title, a_id, a_replies, a_authorName,a_cat,a_author
from articles
where a_visible > 0 and a_cat = 0 and a_Date > adddate(now(),-30) * 1 order by a_id desc limit 0,4)b
union
select * from (
select a_title, a_id, a_replies, a_authorName,a_cat,a_author
from articles
where a_visible > 0 and a_cat = 1 and a_Date > adddate(now(),-30) * 1 order by a_id desc limit 0,4)c
) a
inner join authors au on a.a_author = au.au_id
order by a.a_id desc limit 0,8
1 PRIMARY <derived2> ALL null null null null 8 Using filesort
1 PRIMARY au ref PRIMARY PRIMARY 4 a.a_author 8 Using where
2 DERIVED <derived3> ALL null null null null 4
3 DERIVED articles ALL Cat,getLast,cat-date-id null null null 9955 Using filesort
4 UNION <derived5> ALL null null null null 4
5 DERIVED articles ALL Cat,getLast,cat-date-id null null null 9955 Using filesort
0 UNION RESULT <union2,4> ALL null null null null null
explain select a_title, a_id, a_replies, a_authorName,a_cat,a_author
from articles
where a_visible > 0 and a_cat = 1 and a_Date > adddate(now(),-30) * 1 order by a_id desc limit 0,4
1 SIMPLE articles range Cat,getLast,cat-date-id Cat 16 null 26 Using where; Using filesort
In the first explain: 9955 rows.
In the second: 26 rows.
Suggested fix:
Fix the optimizer to select the best way and/or change the "explain" to show correct results.
Description: When I execute explain direct on the query, I get diffrent results from the big query with the devired tables. How to repeat: CREATE TABLE `articles` ( `a_id` int(11) NOT NULL AUTO_INCREMENT, `a_title` varchar(90) NOT NULL DEFAULT '', `a_date` bigint(20) NOT NULL DEFAULT '0', `a_cat` double(4,0) NOT NULL DEFAULT '0', `a_short` varchar(400) DEFAULT NULL, `a_replies` smallint(6) NOT NULL DEFAULT '0', `a_visible` smallint(1) NOT NULL DEFAULT '0' COMMENT '0 - disabeld; 1- articles; 2 - torah', `a_author` smallint(6) NOT NULL DEFAULT '0', `a_authorName` varchar(75) NOT NULL DEFAULT '', `a_content` mediumtext NOT NULL, `a_special` int(11) NOT NULL DEFAULT '0', `a_image` int(11) NOT NULL DEFAULT '0', `a_upDate` bigint(14) NOT NULL DEFAULT '0', `a_nlsent` int(1) unsigned NOT NULL DEFAULT '0', `a_tags` varchar(255) DEFAULT NULL, `a_largeImage` int(11) unsigned DEFAULT '0', `a_audio` smallint(1) unsigned zerofill NOT NULL DEFAULT '0', `a_album` smallint(1) unsigned zerofill NOT NULL DEFAULT '0', `a_video` smallint(1) unsigned zerofill NOT NULL DEFAULT '0', PRIMARY KEY (`a_id`), KEY `Cat` (`a_cat`,`a_date`), KEY `author` (`a_author`), KEY `autors` (`a_author`,`a_date`), KEY `getLast` (`a_visible`,`a_cat`,`a_date`), KEY `cat-date-id` (`a_cat`,`a_date`) ) ENGINE=MyISAM AUTO_INCREMENT=10029 DEFAULT CHARSET=utf8$$ CREATE TABLE `authors` ( `au_id` int(11) NOT NULL AUTO_INCREMENT, `au_name` text NOT NULL, `au_description` text, `au_isConst` smallint(6) NOT NULL DEFAULT '0', `au_image` int(11) NOT NULL DEFAULT '0', `au_lname` varchar(53) NOT NULL DEFAULT '', `au_url` varchar(105) NOT NULL DEFAULT '', `au_email` varchar(45) NOT NULL DEFAULT '', `au_pref` varchar(30) NOT NULL DEFAULT '', `au_short` varchar(14) NOT NULL, PRIMARY KEY (`au_id`,`au_isConst`) USING BTREE ) ENGINE=MyISAM AUTO_INCREMENT=872 DEFAULT CHARSET=utf8$$ /* Fill tables .... */ explain select a.*,au_id,au_image from ( select * from ( select a_title, a_id, a_replies, a_authorName,a_cat,a_author from articles where a_visible > 0 and a_cat = 0 and a_Date > adddate(now(),-30) * 1 order by a_id desc limit 0,4)b union select * from ( select a_title, a_id, a_replies, a_authorName,a_cat,a_author from articles where a_visible > 0 and a_cat = 1 and a_Date > adddate(now(),-30) * 1 order by a_id desc limit 0,4)c ) a inner join authors au on a.a_author = au.au_id order by a.a_id desc limit 0,8 1 PRIMARY <derived2> ALL null null null null 8 Using filesort 1 PRIMARY au ref PRIMARY PRIMARY 4 a.a_author 8 Using where 2 DERIVED <derived3> ALL null null null null 4 3 DERIVED articles ALL Cat,getLast,cat-date-id null null null 9955 Using filesort 4 UNION <derived5> ALL null null null null 4 5 DERIVED articles ALL Cat,getLast,cat-date-id null null null 9955 Using filesort 0 UNION RESULT <union2,4> ALL null null null null null explain select a_title, a_id, a_replies, a_authorName,a_cat,a_author from articles where a_visible > 0 and a_cat = 1 and a_Date > adddate(now(),-30) * 1 order by a_id desc limit 0,4 1 SIMPLE articles range Cat,getLast,cat-date-id Cat 16 null 26 Using where; Using filesort In the first explain: 9955 rows. In the second: 26 rows. Suggested fix: Fix the optimizer to select the best way and/or change the "explain" to show correct results.