Professional Web Applications Themes

MS Access query in MySQL - MySQL

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 ...

  1. #1

    Default MS Access query in MySQL

    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.

    Peter Guest

  2. #2

    Default Re: MS Access query in MySQL

    On May 10, 1:43 pm, Peter <com> wrote: 
    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.

    nk Guest

  3. #3

    Default Re: MS Access query in MySQL

    On 10 May, 12:43, Peter <com> wrote: 

    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;

    Captain Guest

  4. #4

    Default Re: MS Access query in MySQL

    On May 10, 10:04 pm, nk <com> wrote: 


    >
    > 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.[/ref]

    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.


    Peter Guest

Similar Threads

  1. Replies: 2
    Last Post: March 22nd, 03:38 AM
  2. Replies: 3
    Last Post: August 10th, 01:13 AM
  3. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  4. Replies: 0
    Last Post: November 4th, 05:38 PM
  5. Query / mySQL / Access
    By John Smith in forum ASP
    Replies: 1
    Last Post: July 18th, 03:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139