MySQL Forums
Forum List  »  Knowledge Base

Slow group query after add index
Posted by: irwan
Date: December 11, 2008 05:06PM

Im creating table like this

CREATE TABLE `tblinv` (
`tglorder` date NOT NULL,
`kdsls` varchar(7) NOT NULL,
`noorder` varchar(10) NOT NULL,
`tipe` varchar(1) NOT NULL,
`tglinv` date NOT NULL,
`noinv` varchar(10) default '',
`noinvrtr` varchar(10) default NULL,
`tglinvrtr` varchar(16) default NULL,
`jthtmp` date NOT NULL,
`paytipe` varchar(1) default '',
`kd_outlet` varchar(7) NOT NULL,
`norkp` varchar(10) default '',
`norkporder` varchar(10) default '',
`nopo` varchar(10) default NULL,
`prn` varchar(1) NOT NULL,
`btl` varchar(1) NOT NULL,
`ttlgross` double NOT NULL,
`disc1` double NOT NULL,
`disc2` double NOT NULL,
`disc3` double NOT NULL,
`promo` double NOT NULL,
`cashdisc` double NOT NULL default '0',
`ttlnett` double NOT NULL,
`ppn` double NOT NULL,
`ppnbm` double NOT NULL,
`ttlall` double NOT NULL,
`ttlallinp` double NOT NULL,
`diff` double NOT NULL,
`konsy` varchar(1) NOT NULL,
`byr` double NOT NULL,
`nopjk` varchar(20) default '',
`usr` varchar(10) NOT NULL,
`kdslskum` varchar(7) NOT NULL,
`kd_outletkum` varchar(7) NOT NULL,
`tgh` varchar(1) default '',
`kum` date NOT NULL,
`tglpjk` date default NULL,
`ttlpotpjk` double default '0',
`ttlrpmk` double default '0',
`ttldpp` double default '0',
`ttlppn` double default '0',
`ket` varchar(30) default NULL,
`tipepjk` varchar(2) default NULL,
`ketpjk` varchar(20) default NULL,
`expjk` varchar(10) default NULL,
`ttlgrosspjk` double default '0',
`tglbtl` date default NULL,
`tglreal` date default NULL,
`noreal` varchar(10) default NULL,
`tipebyr` varchar(1) default '0',
`TIPESLS` varchar(2) default NULL,
`flag` varchar(1) default '0',
`tglorderx` date NOT NULL,
`NOMIGO` varchar(100) NOT NULL default '',
`POD` varchar(1) NOT NULL,
`tagihan` date default NULL,
`gross_gl` double NOT NULL,
`ttlall_gl` double NOT NULL,
PRIMARY KEY (`tglorder`,`kdsls`,`noorder`,`tipe`),
KEY `Index_tblinv_kd_sls` USING BTREE (`kdsls`),
KEY `Index_tblinv_noorder` USING BTREE (`noorder`),
KEY `Index_tblinv_tipe` (`tipe`),
KEY `Index_tblinv_tglinv` USING BTREE (`tglinv`),
KEY `Index_tblinv_noinv` USING BTREE (`noinv`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=0;

when i execute this command get 2 seconds :

select kd_outlet, sum(ttlall-byr) from tblinv where ttlall<>byr group by kd_outlet;

but when i add new index :

ALTER TABLE `tblinv` ADD INDEX `Index_tblinv_kd_outlet`(`kd_outlet`);

i got slowest query about 8 minutes... so i drop it again and get 2 second result
so why it's go to slowly.... my other report using "group by kd_outlet" got slowly with full scan table too

my tables contain 180.000-190.000 records

i'm trying import on Oracle 10Gi, it normal fast result with/out index

or should i change my query like this, i got fast result 18 sec with index :

select kd_outlet, sum(ttlall-byr) from
(
select kd_outlet, ttlall, byr from tblinv where ttlall<>byr
) as xxx
group by xxx.kd_outlet

or i see on manual, should i use sintax IGNORE INDEX... could any body give sample....

Rgrds

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow group query after add index
3923
December 11, 2008 05:06PM


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.