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

[ghasem]

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

[ghasem]

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

[ghasem]

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