List:General Discussion« Previous MessageNext Message »
From:SGreen Date:May 25 2005 1:31pm
Subject:Re: Sub Query to long...
View as plain text  
Hendro Suryawan <elcom@stripped> wrote on 05/25/2005 06:23:52 PM:

> Hi Mathias,
> Thanks for your suggestion, but i run this query to find multiple 
> records with the same name in field barang (double records). And the 
> results i found 94 rows at 54813 ms. I try your idea and the result is 
> the same. So i think mysql not optimized for this kind sub query.
> Do you have any other suggestion?
> 
> hendro
> 
> mfatene@stripped wrote:
> 
>  >hi,
>  >don't listen to last email.
>  >
>  >since the two first rows are unique, you can't use my example.
>  >
>  >Just create an index as i said, and play your query :
>  >Select BrgId, Kode, Barang From Barang Where Barang in
>  > (Select Barang From Barang  Group By Barang Having Count(*) > 1 )
>  >
>  >Mathias
>  >
>  >Selon mfatene@stripped:
>  >
>  > 
>  >
>  >>I rerezad you and discovered that (BrgId, Kode) is UNIQUE.
>  >>your query will return no rows :o)
>  >>
>  >>spending 54813 ms for nothing.
>  >>
>  >>Mathias
>  >>
>  >>Selon mfatene@stripped:
>  >>
>  >> 
>  >>
>  >>>Hi,
>  >>>You may have the same table structure in MS, but not the same table
>  >>> 
>  >>>
>  >>definiton
>  >> 
>  >>
>  >>>:
>  >>>constraints+indexes+stats !
>  >>>
>  >>>try :
>  >>>create index toto on Barang(BrgId, Kode, Barang);
>  >>>Select BrgId, Kode, Barang From Barang
>  >>>  Group By Barang
>  >>>  Having Count(*) > 1 ;
>  >>>
>  >>>Mathias
>  >>>
>  >>>Selon Hendro Suryawan <elcom@stripped>:
>  >>>
>  >>> 
>  >>>
>  >>>>Hi,
>  >>>>I have 8414 records in table name Barang, I run query like this :
>  >>>>
>  >>>>Select BrgId, Kode, Barang From Barang Where Barang in
>  >>>>(Select Barang From Barang  Group By Barang Having Count(*) > 1
> )
>  >>>>
>  >>>> and the answer took 54813 ms. I think is too long. I ran the same 
> query
>  >>>>against same table in MS SQL Server it took 1 second. Does anyone 
have
>  >>>>any idea?
>  >>>>
>  >>>>I have mysql on FC 3 X86_64 on Athlon 64, RAM 512 MB.
>  >>>>Table Definition :
>  >>>>CREATE TABLE `Barang` (
>  >>>>  `BrgId` int(4) NOT NULL default '0',
>  >>>>  `Kode` varchar(11) NOT NULL default '',
>  >>>>  `Barang` varchar(70) NOT NULL default '',
>  >>>>  `Satuan` varchar(10) default NULL,
>  >>>>  `Stok` decimal(10,0) default NULL,
>  >>>>  `Nilai` decimal(10,0) default NULL,
>  >>>>  `Lokal` char(1) default NULL,
>  >>>>  `SGrupId` int(10) NOT NULL default '0',
>  >>>>  `StokMin` int(10) default NULL,
>  >>>>  `Catatan` blob,
>  >>>>  `Benda` char(1) NOT NULL default '',
>  >>>>  `Gambar` longblob,
>  >>>>  `LastTrans` date default NULL,
>  >>>>  `Operators` varchar(50) default NULL,
>  >>>>  PRIMARY KEY  (`BrgId`),
>  >>>>  UNIQUE KEY `Kode` (`Kode`),
>  >>>>  KEY `Barang` (`Barang`)
>  >>>>) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>  >>>>
>  >>>>
>  >>>>thanks,
>  >>>>Hendro
>  >>>>

My suggestion: Don't use a subquery, use a temp table (
http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html)

CREATE TEMPORARY TABLE tmpDupes (KEY (`Barang`))
SELECT `Barang`
FROM Barang
GROUP BY Barang
HAVING count(1) >1;

Select b.`BrgId`, b.`Kode`, b.`Barang` 
From Barang b
INNER JOIN tmpDupes d
        on d.`Barang`= b.`Barang`;

DROP TEMPORARY TABLE tmpDupes;

Sure it takes 3 statements but it's going to be MUCH faster than 54 
seconds (possibly < 1 second). It's how the subquery *should* have 
executed your statement but I don't think it did.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
Query to select...Dan Bolser24 May
  • Re: Query to select...mfatene24 May
    • Re: Query to select...Michael Stassen24 May
      • Re: Query to select...mfatene24 May
    • Re: Query to select...Dan Bolser24 May
    • Re: Query to select...Dan Bolser25 May
  • Re: Query to select...SGreen24 May
Re: Query to select...Michael Stassen24 May
  • Re: Query to select...SGreen24 May
    • Re: Query to select...Dan Bolser24 May
      • Sub Query to long...Hendro Suryawan25 May
        • Re: Sub Query to long...mfatene25 May
          • Re: Sub Query to long...mfatene25 May
            • Re: Sub Query to long...mfatene25 May
              • Re: Sub Query to long...Hendro Suryawan25 May
                • Re: Sub Query to long...SGreen25 May
                  • Re: Sub Query to long...[solved]Hendro Suryawan26 May