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
