Re: Why so great difference in so simple query ?

[Rick James]

please provide
* SHOW CREATE TABLE tbl\G -- easier to read than SHOW INDEXES
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Perhaps the answer is that the first query simplifies:
`state` = (
-> SELECT
-> DISTINCT `loc`.`state_code`
-> FROM
-> `hp_location` as `loc`
-> WHERE
-> `loc`.`location_type` = "city"
-> AND `loc`.`state` = "New York"
-> );
boils down to simply state='New York' and there is an index in zipcodes on state.