Hi all,

I have just migrated all of my data from an MS Access database to
MySQL using Navicat.

There is a query in MS Access that I use to find duplicate words in my
table. The SQL generated by MS Access is:

SELECT vocab.English, vocab.POS, vocab.Kana, vocab.Kanji, vocab.jlpt,
vocab.cardNum, vocab.dEntered
FROM vocab
WHERE (((vocab.Kana) In (SELECT kana FROM vocab As Tmp GROUP BY kana
HAVING Count(*)>1 )))
ORDER BY vocab.Kana;

When I put this same query into MySQL the CPU usage goes up to 100%
and takes forever to complete (actually I haven't waited forever but
it takes a long time and then I quit the query).

Can someone tell me why this happens and what would be an equivalent
query to use in MySQL?

I am using MySQL server version: 5.0.27-community-nt.

Many thanks,

Peter.

On May 10, 1:43 pm, Peter <petethegai...@gmail.com> wrote:
> Hi all,
>
> I have just migrated all of my data from an MS Access database to
> MySQL using Navicat.
>
> There is a query in MS Access that I use to find duplicate words in my
> table. The SQL generated by MS Access is:
>
> SELECT vocab.English, vocab.POS, vocab.Kana, vocab.Kanji, vocab.jlpt,
> vocab.cardNum, vocab.dEntered
> FROM vocab
> WHERE (((vocab.Kana) In (SELECT kana FROM vocab As Tmp GROUP BY kana
> HAVING Count(*)>1 )))
> ORDER BY vocab.Kana;
Do you have an index on vocab.kana? Otherwise, depending on the size
of your database, MySQL might have to sort through a lot of rows,
which could explain the high CPU usage.

On 10 May, 12:43, Peter <petethegai...@gmail.com> wrote:
> Hi all,
>
> I have just migrated all of my data from an MS Access database to
> MySQL using Navicat.
>
> There is a query in MS Access that I use to find duplicate words in my
> table. The SQL generated by MS Access is:
>
> SELECT vocab.English, vocab.POS, vocab.Kana, vocab.Kanji, vocab.jlpt,
> vocab.cardNum, vocab.dEntered
> FROM vocab
> WHERE (((vocab.Kana) In (SELECT kana FROM vocab As Tmp GROUP BY kana
> HAVING Count(*)>1 )))
> ORDER BY vocab.Kana;
>
> When I put this same query into MySQL the CPU usage goes up to 100%
> and takes forever to complete (actually I haven't waited forever but
> it takes a long time and then I quit the query).
>
> Can someone tell me why this happens and what would be an equivalent
> query to use in MySQL?
>
> I am using MySQL server version: 5.0.27-community-nt.
>
> Many thanks,
>
> Peter.

Regardless of what nk says, I would convert this to a self join query.
Untested:
SELECT
vocab.English,
vocab.POS,
vocab.Kana,
vocab.Kanji,
vocab.jlpt,
vocab.cardNum,
vocab.dEntered
FROM vocab v1
JOIN vocab v2 USING(Kana)
HAVING Count(*)>1
ORDER BY v1.Kana;

On May 10, 10:04 pm, nk <nkoell...@gmail.com> wrote:
> On May 10, 1:43 pm, Peter <petethegai...@gmail.com> wrote:> Hi all,
>
> > I have just migrated all of my data from an MS Access database to
> > MySQL using Navicat.
>
> > There is a query in MS Access that I use to find duplicate words in my
> > table. The SQL generated by MS Access is:
>
> > SELECT vocab.English, vocab.POS, vocab.Kana, vocab.Kanji, vocab.jlpt,
> > vocab.cardNum, vocab.dEntered
> > FROM vocab
> > WHERE (((vocab.Kana) In (SELECT kana FROM vocab As Tmp GROUP BY kana
> > HAVING Count(*)>1 )))
> > ORDER BY vocab.Kana;
>
> Do you have an index on vocab.kana? Otherwise, depending on the size
> of your database, MySQL might have to sort through a lot of rows,
> which could explain the high CPU usage.

Yeah Navicat created three indexes on this table when I imported the
data.
Should these indexes be a special type? I have the options in Navicat
of "Normal", "Unique" & "Full Text" However when I try to change them
to "Full Text" it says the table type doesn't support this kind of
index.