469,347 Members | 6,633 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

mySQL 4.02 - select statement on non primary field takes incredibly longtime - why ?

Dear mySQL community,
I have once again turned to the user groups for a problem I cannot
solve myself! Atually, I have read all I can from the newsgroups on
this but I could not solve it myself.
Here it is:
mySQL server: version "4.0.20-standard" on localhost
hardware : 1.8 MHz Pentium 4 with 512 cache and 512 MB 266MHz RAM and
40 MB Matrox+ harddisk

Here is the problem:
There is a table which I has 7 fields, 3000 records. One field is
auto_number and is PRIMARY, second is an integer(12) field and is KEY,
and the rest are varchar(255) fields. - This is the OLDTABLE
I have created a new table, and repeated the table I just described
over and over into this new table, there are now more than 3.6 million
records. - This is the new NEWTABLE
When I run a select like "select * from NEWTABLE where
auto_number="BLAHBLAH" it returns the record in miliseconds.
But when I query the integer(12) field "SELECT dnjNo FROM NEWTABLE
where dnjNo='$dnjno' limit 25", it takes 2 and half minutes to tell me
if the record does not exist, but only if does not exist, if it
exists, then the 25 rows are returned in about 3 seconds and then in
0.2 seconds for subsequent search.
Why does it take so long to tell me if a record does not exist?
Any suggestions on how I can improve this performance, since without
this how can I basically search for non-existant rows? Did I forget to
say that the CPU is running at full during the 2 and half minute.
Please help!
ghasem
Jul 20 '05 #1
2 2286
gh************@yahoo.com (ghasem) wrote in message news:<e9**************************@posting.google. com>...
Dear mySQL community,
I have once again turned to the user groups for a problem I cannot
solve myself! Atually, I have read all I can from the newsgroups on
this but I could not solve it myself.
Here it is:
mySQL server: version "4.0.20-standard" on localhost
hardware : 1.8 MHz Pentium 4 with 512 cache and 512 MB 266MHz RAM and
40 MB Matrox+ harddisk

Here is the problem:
There is a table which I has 7 fields, 3000 records. One field is
auto_number and is PRIMARY, second is an integer(12) field and is KEY,
and the rest are varchar(255) fields. - This is the OLDTABLE
I have created a new table, and repeated the table I just described
over and over into this new table, there are now more than 3.6 million
records. - This is the new NEWTABLE
When I run a select like "select * from NEWTABLE where
auto_number="BLAHBLAH" it returns the record in miliseconds.
But when I query the integer(12) field "SELECT dnjNo FROM NEWTABLE
where dnjNo='$dnjno' limit 25", it takes 2 and half minutes to tell me
if the record does not exist, but only if does not exist, if it
exists, then the 25 rows are returned in about 3 seconds and then in
0.2 seconds for subsequent search.
Why does it take so long to tell me if a record does not exist?
Any suggestions on how I can improve this performance, since without
this how can I basically search for non-existant rows? Did I forget to
say that the CPU is running at full during the 2 and half minute.
Please help!
ghasem


A while ago I posted the message above. The problem was that the
fields which are defined as KEY could not be searched at fast as a KEY
should really be searched. Now I have resolved the problem which was
actually my mistake and the the field which was defined as key was not
atually being searched, but the select was being preformed on a
non-KEY field and that was the reason it was slow.
Now I decided to make all fields as KEY, there are also 6 varchar(255)
fields in the table which I have now defined them as FULLTEXT. But the
peformance is once again not fast enough. A select statement on the
integet field which is defined as KEY takes 0.01 second to tell if the
recod exists or not. A similar select statement with three
varchar(255) fields ANDed together takes much more time, close to 2
minutes. Is this normal behavious, or am I missing something.
ghasem
Jul 20 '05 #2
gh************@yahoo.com (ghasem) wrote in message news:<e9**************************@posting.google. com>...
Dear mySQL community,
I have once again turned to the user groups for a problem I cannot
solve myself! Atually, I have read all I can from the newsgroups on
this but I could not solve it myself.
Here it is:
mySQL server: version "4.0.20-standard" on localhost
hardware : 1.8 MHz Pentium 4 with 512 cache and 512 MB 266MHz RAM and
40 MB Matrox+ harddisk

Here is the problem:
There is a table which I has 7 fields, 3000 records. One field is
auto_number and is PRIMARY, second is an integer(12) field and is KEY,
and the rest are varchar(255) fields. - This is the OLDTABLE
I have created a new table, and repeated the table I just described
over and over into this new table, there are now more than 3.6 million
records. - This is the new NEWTABLE
When I run a select like "select * from NEWTABLE where
auto_number="BLAHBLAH" it returns the record in miliseconds.
But when I query the integer(12) field "SELECT dnjNo FROM NEWTABLE
where dnjNo='$dnjno' limit 25", it takes 2 and half minutes to tell me
if the record does not exist, but only if does not exist, if it
exists, then the 25 rows are returned in about 3 seconds and then in
0.2 seconds for subsequent search.
Why does it take so long to tell me if a record does not exist?
Any suggestions on how I can improve this performance, since without
this how can I basically search for non-existant rows? Did I forget to
say that the CPU is running at full during the 2 and half minute.
Please help!
ghasem


A while ago I posted the message above. The problem was that the
fields which are defined as KEY could not be searched at fast as a KEY
should really be searched. Now I have resolved the problem which was
actually my mistake and the the field which was defined as key was not
atually being searched, but the select was being preformed on a
non-KEY field and that was the reason it was slow.
Now I decided to make all fields as KEY, there are also 6 varchar(255)
fields in the table which I have now defined them as FULLTEXT. But the
peformance is once again not fast enough. A select statement on the
integet field which is defined as KEY takes 0.01 second to tell if the
recod exists or not. A similar select statement with three
varchar(255) fields ANDed together takes much more time, close to 2
minutes. Is this normal behavious, or am I missing something.
ghasem
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Mike Chirico | last post: by
11 posts views Thread by DJJ | last post: by
9 posts views Thread by elyob | last post: by
1 post views Thread by paulq182 | last post: by
Atli
6 posts views Thread by Atli | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.