# Apparently bizarre query plan joins all rows of huge table to tiny table **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [May 3, 2010, 3:47pm UTC](https://forums.percona.com/t/apparently-bizarre-query-plan-joins-all-rows-of-huge-table-to-tiny-table/1399 "2010-05-03T15:47:19Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![andysk](https://avatars.discourse-cdn.com/v4/letter/a/bbe5ce/32.png) [@andysk](https://forums.percona.com/u/andysk)\ **Post date:** [May 3, 2010, 3:47pm UTC](https://forums.percona.com/t/apparently-bizarre-query-plan-joins-all-rows-of-huge-table-to-tiny-table/1399/1 "2010-05-03T15:47:19Z")
Hello, I would love some help understanding what I believe to be a bizarre query plan, and how I can avoid it. The table alerts\_ad\_staging1 is huge (~420M rows), and the table ads\_page\_group is joined in, but it only has 4 rows. If I remove the join, the query uses the primary key against the alerts\_ad\_staging1 table, as I would expect. This query should select about 1.8M rows, so the estimated 47807 rows in the second step of the query plan makes no sense to me. mysql version: mysql Ver 14.14 Distrib 5.1.31, for debian-linux-gnu (x86\_64) using EditLine wrapperquery:explain extended select aa.id, ads\_page\_group.name from alerts\_ad\_staging1 aa join ads\_page\_group on aa.page\_group\_id = ads\_page\_group.id where aa.id between 418536945 and 420368655\Gquery plan:\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: ads\_page\_group type: ALLpossible\_keys: PRIMARY key: NULL key\_len: NULL ref: NULL rows: 4 filtered: 100.00 Extra:\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: aa type: refpossible\_keys: PRIMARY,alerts\_ad\_page\_group\_id key: alerts\_ad\_page\_group\_id key\_len: 1 ref: bv.ads\_page\_group.id rows: 47807 filtered: 100.00 Extra: Using where; Using index\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row _ **table definitions:mysql\> show create table ads\_page\_group\G** _ 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: ads\_page\_groupCreate Table: CREATE TABLE `ads_page_group` ( `id` tinyint(3) unsigned NOT NULL AUTO\_INCREMENT, `name` varchar(6) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO\_INCREMENT=5 DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql\> select \* from ads\_page\_group;±—±-------+| id | name |±—±-------+| 1 | Top | | 2 | Right | | 3 | Left | | 4 | Bottom | ±—±-------+4 rows in set (0.00 sec)CREATE TABLE `alerts_ad_staging1` ( `id` int(11) NOT NULL AUTO\_INCREMENT,…other columns not referenced by this query… `page_group_id` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`,`date`),…other keys not referenced by this query… KEY `alerts_ad_page_group_id` (`page_group_id`),) ENGINE=InnoDB AUTO\_INCREMENT=426916572 DEFAULT CHARSET=utf8 COLLATE=utf8\_bin/\*!50100 PARTITION BY RANGE (TO\_DAYS(date))(PARTITION All2008 VALUES LESS THAN (733773) ENGINE = InnoDB, PARTITION Jan09 VALUES LESS THAN (733804) ENGINE = InnoDB, PARTITION Feb09 VALUES LESS THAN (733832) ENGINE = InnoDB, PARTITION Mar09 VALUES LESS THAN (733863) ENGINE = InnoDB, PARTITION Apr09 VALUES LESS THAN (733893) ENGINE = InnoDB, PARTITION May09 VALUES LESS THAN (733924) ENGINE = InnoDB, PARTITION Jun09 VALUES LESS THAN (733954) ENGINE = InnoDB, PARTITION Jul09 VALUES LESS THAN (733985) ENGINE = InnoDB, PARTITION Aug09 VALUES LESS THAN (734016) ENGINE = InnoDB, PARTITION Sep09 VALUES LESS THAN (734046) ENGINE = InnoDB, PARTITION Oct09 VALUES LESS THAN (734077) ENGINE = InnoDB, PARTITION Nov09 VALUES LESS THAN (734107) ENGINE = InnoDB, PARTITION Dec09 VALUES LESS THAN (734138) ENGINE = InnoDB, PARTITION Jan10 VALUES LESS THAN (734169) ENGINE = InnoDB, PARTITION Feb10 VALUES LESS THAN (734197) ENGINE = InnoDB, PARTITION Mar10 VALUES LESS THAN (734228) ENGINE = InnoDB, PARTITION Apr10 VALUES LESS THAN (734258) ENGINE = InnoDB, PARTITION May10 VALUES LESS THAN (734289) ENGINE = InnoDB, PARTITION Jun10 VALUES LESS THAN (734319) ENGINE = InnoDB, PARTITION Jul10 VALUES LESS THAN (734350) ENGINE = InnoDB, PARTITION Aug10 VALUES LESS THAN (734381) ENGINE = InnoDB, PARTITION Sep10 VALUES LESS THAN (734411) ENGINE = InnoDB, PARTITION Oct10 VALUES LESS THAN (734442) ENGINE = InnoDB, PARTITION Nov10 VALUES LESS THAN (734472) ENGINE = InnoDB, PARTITION Dec10 VALUES LESS THAN (734503) ENGINE = InnoDB, PARTITION After10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) \*/ Thanks in advance for any help. I’m more than happy to provide other information that is helpful. Andy ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [May 3, 2010, 4:13pm UTC](https://forums.percona.com/t/apparently-bizarre-query-plan-joins-all-rows-of-huge-table-to-tiny-table/1399/2 "2010-05-03T16:13:52Z")
What are the execution times? Suppose the query would return only ten rows, then scanning all rows from the table aa is too expensive. In that case the current plan could be much better, given that this index is present. ALTER TABLE aa ADD KEY `alerts_ad_page_group_id_id` (`page_group_id`, `id`) If all or almost all rows in aa will be returned, as is the case in your situation, then indeed the execution plan is bad. This query would run better: select aa.id, ads\_page\_group.name from alerts\_ad\_staging1 aa straight\_join ads\_page\_group on aa.page\_group\_id = ads\_page\_group.id where aa.id between 418536945 and 420368655 ---
**Author:** ![andysk](https://avatars.discourse-cdn.com/v4/letter/a/bbe5ce/32.png) [@andysk](https://forums.percona.com/u/andysk)\ **Post date:** [May 3, 2010, 4:49pm UTC](https://forums.percona.com/t/apparently-bizarre-query-plan-joins-all-rows-of-huge-table-to-tiny-table/1399/3 "2010-05-03T16:49:44Z")
good questions, The query should return ~1.8M rows, which is about 1/233rd of the table. All of these rows should be contiguous on disk. execution times: (edited to reflect recent test) query with no join: 4 seconds query with straight\_join: 4 seconds query with join: 58 minutes (wow - big difference vs straight\_join!) Indeed, using straight\_join produces the query plan I expected to see in the first place (below). But I am dying to know why the optimizer would choose the plan it did? mysql\> explain extended select aa.id, ads\_page\_group.name from alerts\_ad\_staging1 aa straight\_join ads\_page\_group on aa.page\_group\_id = ads\_page\_group.id where aa.id between 418536945 and 420368655\G\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: aa type: rangepossible\_keys: PRIMARY,alerts\_ad\_page\_group\_id key: PRIMARY key\_len: 4 ref: NULL rows: 5123229 filtered: 100.00 Extra: Using where\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: ads\_page\_group type: ALLpossible\_keys: PRIMARY key: NULL key\_len: NULL ref: NULL rows: 4 filtered: 75.00 Extra: Using where; Using join buffer2 rows in set, 1 warning (0.01 sec) Thanks, Andy ---
**Author:** ![andysk](https://avatars.discourse-cdn.com/v4/letter/a/bbe5ce/32.png) [@andysk](https://forums.percona.com/u/andysk)\ **Post date:** [May 11, 2010, 4:52pm UTC](https://forums.percona.com/t/apparently-bizarre-query-plan-joins-all-rows-of-huge-table-to-tiny-table/1399/4 "2010-05-11T16:52:42Z")
That makes sense as well Baron. Thanks again.