List:General Discussion« Previous MessageNext Message »
From:luiz Rafael Date:June 21 2006 3:25pm
Subject:query slow
View as plain text  
Dear friends

is their any way to optimize this query bellow, it take +- 2minutes do 
complete, i think it becouse their no index by the emissao field

SELECT  * FROM  `sav00_sava0400_dbf`   WHERE 2000 = YEAR(`emissao`) OR (1999 
= YEAR(`emissao`) AND 12 < MONTH(`emissao`))  ORDER BY emissao ASC

The table structure is bellow
CREATE TABLE `sav00_sava0400_dbf` (
  `unidade` double(2,0) default NULL,
  `duplicata` double(6,0) default NULL,
  `ordem` double(2,0) default NULL,
  `unidadeped` double(2,0) default NULL,
  `pedido` double(6,0) default NULL,
  `emissao` date default NULL,
  `vencto` date default NULL,
  `venctoorig` date default NULL,
  `cliente` double(5,0) default NULL,
  `cidade` double(4,0) default NULL,
  `estado` char(2) default NULL,
  `regiao` double(2,0) default NULL,
  `microregia` double(2,0) default NULL,
  `represent` double(3,0) default NULL,
  `comissao` double(5,2) default NULL,
  `valorface` double(15,2) default NULL,
  `valorbaixa` double(15,2) default NULL,
  `produtogrp` char(3) default NULL,
  `codbaixa` double(2,0) default NULL,
  `ocorrencia` double(2,0) default NULL,
  `databaixa` date default NULL,
  `jurosdev` double(15,2) default NULL,
  `jurospagos` double(15,2) default NULL,
  `dliquidupl` date default NULL,
  `jurospend` double(15,2) default NULL,
  `jurosmerc` double(15,2) default NULL,
  `saldodupl` double(15,2) default NULL,
  `jurosabona` double(15,2) default NULL,
  `statusfina` double(2,0) default NULL,
  `dstatusfin` date default NULL,
  `valorfatu` double(15,2) default NULL,
  `trans` date default NULL,
  `proc004` date default NULL,
  `agcobr` double(3,0) default NULL,
  `sitdupl` double(2,0) default NULL,
  `valordevol` double(15,2) default NULL,
  `valordesc` double(15,2) default NULL,
  `sr_recno` bigint(15) NOT NULL auto_increment,
  `sr_deleted` char(1) NOT NULL default '',
  `indkey_001` char(254) default NULL,
  UNIQUE KEY `sr_recno` (`sr_recno`),
  KEY `SAV00_SAVI0401_000001` (`unidade`,`duplicata`,`ordem`,`sr_recno`),
  KEY `SAV00_SAVI0402_000002` (`saldodupl`,`sr_recno`),
  KEY `SAV00_SAVI0403_000003` (`proc004`,`sr_recno`),
  KEY `SAV00_SAVI0404_000004` (`indkey_001`),
  KEY `SAV00_SAVI0405_000005` 
(`represent`,`emissao`,`unidade`,`duplicata`,`ordem`,`sr_recno`),
  KEY `SAV00_SAVI0406_000006` (`vencto`,`sr_recno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Regards
Luiz

Thread
Just need script for creating tablesXiaobo Chen21 Jun
  • Re: Just need script for creating tablesDaniel da Veiga21 Jun
  • RE: Just need script for creating tablesPeter Lauri21 Jun
    • query slowluiz Rafael21 Jun
      • Re: query slowJay Pipes21 Jun
        • Re: query slowEugene Kosov21 Jun
      • Re: query slowEugene Kosov21 Jun
    • Re: query slowluiz Rafael21 Jun
    • reclaim disk spaceluiz Rafael21 Jun
      • Re: reclaim disk spaceDan Buettner21 Jun
      • Re: reclaim disk spaceDan Nelson21 Jun
    • Re: query slowluiz Rafael25 Jun
  • Re: Just need script for creating tablesMartin Jespersen21 Jun
RE: reclaim disk spaceGeorge Law21 Jun