MySQL Forums
Forum List  »  Optimizer & Parser

Optimize two queries on same table with group by and order by
Posted by: Jan D
Date: July 22, 2011 09:10AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimize two queries on same table with group by and order by
2998
July 22, 2011 09:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.