MySQL selects specific columns more slowly than select *

Question

My MySQL is not strong, so please forgive any rookie mistakes. Short version:

SELECT locId, count, avg FROM destAgg_geo is much slower than SELECT * from destAgg_geo

prtt.destAgg is a table with a key on dst_ip (PRIMARY)

mysql> describe prtt.destAgg; +---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | dst_ip | int(10) unsigned | NO | PRI | 0 | | | total | float unsigned | YES | | NULL | | | avg | float unsigned | YES | | NULL | | | sqtotal | float unsigned | YES | | NULL | | | sqavg | float unsigned | YES | | NULL | | | count | int(10) unsigned | YES | | NULL | | +---------+------------------+------+-----+---------+-------+ 

geoip.blocks is a table with a key for startIpNum and endIpNum (PRIMARY)

 mysql> describe geoip.blocks; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | startIpNum | int(10) unsigned | NO | MUL | NULL | | | endIpNum | int(10) unsigned | NO | | NULL | | | locId | int(10) unsigned | NO | | NULL | | +------------+------------------+------+-----+---------+-------+ 

destAgg_geo is a view:

 CREATE VIEW destAgg_geo AS SELECT * FROM destAgg JOIN geoip.blocks ON destAgg.dst_ip BETWEEN geoip.blocks.startIpNum AND geoip.blocks.endIpNum; 

Here's an optimization plan to select *:

 mysql> explain select * from destAgg_geo; +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | 1 | SIMPLE | blocks | ALL | start_end | NULL | NULL | NULL | 3486646 | | | 1 | SIMPLE | destAgg | ALL | PRIMARY | NULL | NULL | NULL | 101893 | Range checked for each record (index map: 0x1) | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ 

Here's an optimization plan to select with specific columns:

 mysql> explain select locId,count,avg from destAgg_geo; +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | 1 | SIMPLE | destAgg | ALL | PRIMARY | NULL | NULL | NULL | 101893 | | | 1 | SIMPLE | blocks | ALL | start_end | NULL | NULL | NULL | 3486646 | Range checked for each record (index map: 0x1) | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ 

Here's the optimization plan for each column from destAgg and only the locId column from geoip.blocks:

 mysql> explain select dst_ip,total,avg,sqtotal,sqavg,count,locId from destAgg_geo; +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | 1 | SIMPLE | blocks | ALL | start_end | NULL | NULL | NULL | 3486646 | | | 1 | SIMPLE | destAgg | ALL | PRIMARY | NULL | NULL | NULL | 101893 | Range checked for each record (index map: 0x1) | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ 

Delete any column except dst_ip and the validation range is reset to blocks:

 mysql> explain select dst_ip,avg,sqtotal,sqavg,count,locId from destAgg_geo; +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | 1 | SIMPLE | destAgg | ALL | PRIMARY | NULL | NULL | NULL | 101893 | | | 1 | SIMPLE | blocks | ALL | start_end | NULL | NULL | NULL | 3486646 | Range checked for each record (index map: 0x1) | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ 

which is then much slower. What's going on here?

(Yes, I could just use the query results and process from there, but I would like to know what is happening and why)

EDIT - EXPLAIN in the VIEW request:

 mysql> explain SELECT * FROM destAgg JOIN geoip.blocks ON destAgg.dst_ip BETWEEN geoip.blocks.startIpNum AND geoip.blocks.endIpNum; +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ | 1 | SIMPLE | blocks | ALL | start_end | NULL | NULL | NULL | 3486646 | | | 1 | SIMPLE | destAgg | ALL | PRIMARY | NULL | NULL | NULL | 101893 | Range checked for each record (index map: 0x1) | +----+-------------+---------+------+---------------+------+---------+------+---------+------------------------------------------------+ 

Answer 1

MySQL can tell you if you run EXPLAIN PLAN for both queries.

The first query with columns does not include any key columns, so I assume that it should execute the SCAN TABLE.

The second query with "SELECT *" includes the primary key, so it can use an index.

Answer 2

The range filter is applied last, so the problem is that the query optimizer first selects to join the larger table in one case, and the smaller one first in another. Perhaps someone with a lot of knowledge of the optimizer can tell us why he joins the tables in a different order for each.

I think the real goal here is to try to get the JOIN to use the index, so the order of the join will not matter much.

Answer 3

I would put the compiler index on locId, count, avg and see if speed would speed up.