Simplify optimization with distinct names
I have a table named card whose primary key is (name, cardset). name is varchar(256); cardset is varchar(3). count(*) from card is 15780; count(distinct name) is 10565.
A lot of queries select from card group by name, often with sum() on some columns that aren't in the primary key. I've created a btree index called cardname on card.name, but even when I force index (cardname), a lot of queries still need heavy manual optimization. For example, the following query takes 9 minutes:
insert into card_totals (name, max_rarity, min_rarity, copies, desired_copies)
select foo.*, desired_copies from (select
`card`.`name` AS `name`,
(select `rarity`.`abbr` AS `abbr` from `rarity` where (`rarity`.`sortvalue` =max(`card`.`rarity` - 1))) AS `max_rarity`,
(select `rarity`.`abbr` AS `abbr` from `rarity` where (`rarity`.`sortvalue` = min((`card`.`rarity` - 1)))) AS `min_rarity`,
(sum(`card`.`foil_copies`) + sum(`card`.`nonfoil_copies`)) AS `copies`
from card force index (cardname) group by name) foo, (select name, least(max(`curmaxcopies`.`max_copies`),36) as `desired_copies` from curmaxcopies force index (cmcname) group by name) bar;
I rewrote it as the following, which takes under 10 seconds:
drop table if exists foo, bar;
create temporary table foo (name varchar(256) primary key, max_rarity varchar(1), min_rarity varchar(1), copies int unsigned) as (select
`card`.`name` AS `name`,
(select `rarity`.`abbr` AS `abbr` from `rarity` where (`rarity`.`sortvalue` =max(`card`.`rarity` - 1))) AS `max_rarity`,
(select `rarity`.`abbr` AS `abbr` from `rarity` where (`rarity`.`sortvalue` = min((`card`.`rarity` - 1)))) AS `min_rarity`,
(sum(`card`.`foil_copies`) + sum(`card`.`nonfoil_copies`)) AS `copies`
from card force index (cardname) group by name);
create temporary table bar (name varchar(256) primary key, desired_copies int unsigned) as (select name, least(max(`curmaxcopies`.`max_copies`),36) as `desired_copies` from curmaxcopies force index (cmcname) group by name);
insert into card_totals (name, max_rarity, min_rarity, copies, desired_copies)
select foo.*, desired_copies from foo left join bar on foo.name = bar.name;
drop table foo, bar;
Is there some way to make this sort of optimization at least partly automatic? NB: curmaxcopies.name is part of the primary key, and distinct curmaxcopies.name = distinct card.name.