Adding an index caused problems!

CREATE TABLE `people` (

`id` INT(11) NULL DEFAULT NULL,
`last_name` VARCHAR(255) NULL DEFAULT NULL,
`first_name` VARCHAR(255) NULL DEFAULT NULL,
`address` VARCHAR(255) NULL DEFAULT NULL,
`city` VARCHAR(255) NULL DEFAULT NULL,
`state` VARCHAR(255) NULL DEFAULT NULL,
`zip` CHAR(10) NULL DEFAULT NULL,
`join_date` DATE NULL DEFAULT NULL,
`last_sale_date` DATE NULL DEFAULT NULL,
[Quoted] [Quoted] `dollars` DECIMAL(10,2) NULL DEFAULT NULL,

	INDEX `inx_join_date` (`join_date`),
	INDEX `inx_full_name` (`last_name`, `first_name`),
	INDEX `inx_dollars` (`dollars`)

Volume: 1,000,000 rows

This query execute in 1.9 sec:

SELECT

	p.state,
	avg(p.dollars)
FROM
	people p
GROUP BY
	p.state

Added state as an index. Now the same query hangs. Explain shows that state is now being used as an index. What's going on under the covers? Received on Wed May 22 2019 - 17:50:36 CEST