Optimize two queries on same table with group by and order by

[Jan D]

Dear MySQL experts:
I do have two queries which operate on a relativley large table (~30Mio rows) and I'd appreciate some ideads how to optimize them. Currently, they run about 2 - 3 minutes. Since they are quite different, I don't have an idea for intelligent indexing. The current ones are just a try (and somewhat related to other queries). The most critical part (in my opinion) is the "use temporary, use filesort" because the query profiler tells me that most of the time is spent there...

Many thanks and regards,
Guido

The table is and queries are:

CREATE TABLE `t1` (
`A`     VARCHAR(20)  NOT NULL DEFAULT '',
`B`     VARCHAR(20)  NOT NULL DEFAULT '',
`E`     VARCHAR(255) NOT NULL DEFAULT '',
`F`     VARCHAR(255) NULL DEFAULT NULL,
`G`     VARCHAR(255) NULL DEFAULT NULL,
`I`     VARCHAR(255) NULL DEFAULT NULL,
`J`     VARCHAR(255) NULL DEFAULT NULL,
`Date1` DATETIME     NOT NULL DEFAULT '0000-00-00 00:00:00',
`Date2` DATETIME     NULL DEFAULT NULL,
`L` VARCHAR(64)      NOT NULL DEFAULT '',
`M` VARCHAR(12)      NOT NULL DEFAULT '',
PRIMARY KEY (`A`, `B`, `E`, `Date1`, `L`, `M`),
INDEX `i1` (`F`),
INDEX `i2` (`L`),
INDEX `i3` (`M`),
INDEX `i4` (`B`),
INDEX `i5` (`E`),
INDEX `i6` (`G`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM

EXPLAIN
SELECT `A` , `G`, `I`, `Date1`, `Date2`
FROM `t1`
WHERE `G` like '48%'
  AND `E` = 'bla'
  AND `L` <> ''
  AND `F` like '%sth%'
GROUP BY `A`
ORDER BY `Date1` DESC

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | t1 | ref | i2,i6,i5 | i5 | 257 | const | 7318 | Using where; Using temporary; Using filesort

explain
select `G`,`E`, `F`, count(*)
from `t1`
where `E` like 'something%'
  and `J` = '45KM'
  and `Date2` > date_sub( curdate(),interval 5 week)
  and `A` like 'U%'
group by `G`,`E`, `F`

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | t1 | range | PRIMARY,i5 | i5 | 257 |NULL| 3743 | Using where; Using temporary; Using filesort

show indexes from t1;

Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
t1 | 0 | PRIMARY | 1 | A     | A |NULL|NULL|NULL| | BTREE |
t1 | 0 | PRIMARY | 2 | B     | A |NULL|NULL|NULL| | BTREE |
t1 | 0 | PRIMARY | 3 | E     | A |NULL|NULL|NULL| | BTREE |
t1 | 0 | PRIMARY | 4 | Date1 | A |NULL|NULL|NULL| | BTREE |
t1 | 0 | PRIMARY | 5 | L     | A |NULL|NULL|NULL| | BTREE |
t1 | 0 | PRIMARY | 6 | M     | A | 29496403 |NULL|NULL| | BTREE |
t1 | 1 | i1      | 1 | F     | A | 14072 |NULL|NULL| YES | BTREE |
t1 | 1 | i2      | 1 | L     | A | 1218 |NULL|NULL| | BTREE |
t1 | 1 | i3      | 1 | M     | A | 278 |NULL|NULL| | BTREE |
t1 | 1 | i4      | 1 | B     | A | 258740 |NULL|NULL| | BTREE |
t1 | 1 | i5      | 1 | E     | A | 84275 |NULL|NULL| | BTREE |
t1 | 1 | i6      | 1 | G     | A | 699 |NULL|NULL| YES | BTREE |

Edited 1 time(s). Last edit at 07/22/2011 09:16AM by Jan D.