Re: Adding an index caused problems!

On 22-5-2019 17:50, rich_at_turftechno.com wrote:
> Here's the DDL:
>
> 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,
> `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?
>

INNODB or MyISAM ?
Which version of MySQL ?

Why do you not show the output of the explain ?

'hangs'.... for how long ?

-- 
Luuk