Hendro Suryawan <[EMAIL PROTECTED]> 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 > > [EMAIL PROTECTED] 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 [EMAIL PROTECTED]: > > > > > > > >>I rerezad you and discovered that (BrgId, Kode) is UNIQUE. > >>your query will return no rows :o) > >> > >>spending 54813 ms for nothing. > >> > >>Mathias > >> > >>Selon [EMAIL PROTECTED]: > >> > >> > >> > >>>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 <[EMAIL PROTECTED]>: > >>> > >>> > >>> > >>>>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
