468,512 Members | 1,504 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Query slower when selecting a different field


Hello,

I have a table called BUILDREQUESTS which I want to select from,
depending on the project ID of each record. The Project ID field is
indexed.

(A) This query runs almost instantly:

SELECT BR.REQID FROM BUILDREQUESTS BR WHERE BR.PROJECTID IN
(1,62,56,3,4,2,15,9,11,17,18,19,22,24,26,63,28,29, 30,32,40,52,68,48,49,37,54,39,73,43,44,77,45,57,55 ,65,66,74,103,78,82,84,85,86,88,90,91,92,93,99,95, 96,97,98,100,104,105,106,107,108,109,110,111);

(B) This query takes about 1.7 seconds:

SELECT BR.REQUESTEDBY FROM BUILDREQUESTS BR WHERE BR.PROJECTID IN
(1,62,56,3,4,2,15,9,11,17,18,19,22,24,26,63,28,29, 30,32,40,52,68,48,49,37,54,39,73,43,44,77,45,57,55 ,65,66,74,103,78,82,84,85,86,88,90,91,92,93,99,95, 96,97,98,100,104,105,106,107,108,109,110,111);

Note that the only difference between Query A and Query B is the field
we are selecting. REQID and REQUESTEDBY by are both Int(11) fields.
The only difference between the two fields is that REQID is indexed.
Neither of these fields are in the WHERE clause and there is no sort
order, so what gives? Why the slowdown? Ideally, I want to select
several more fields, but this is my test case to try and optimize first.

The machine I am running on is fast, single CPU, 512M RAM. MySql 3.23,
default install via Red Hat 8. I have tried tweaking several buffer
settings in my.cnf to no avail.

What am I missing!?

Thanks,
John
Jul 19 '05 #1
2 1884

The only thing I can think of so far, and I'm not sure what to do about
it, is that the table is "Dynamic", not "Fixed". Perhaps jumping around
from record to record is causing the slowdown, as there are multiple
text columns in the table. The select on reqid is fast, because it is
an indexed field and there is no need to go to the table data to get the
value. Problem is that I cannot index every field I want to display.

John
John wrote:

Hello,

I have a table called BUILDREQUESTS which I want to select from,
depending on the project ID of each record. The Project ID field is
indexed.

(A) This query runs almost instantly:

SELECT BR.REQID FROM BUILDREQUESTS BR WHERE BR.PROJECTID IN
(1,62,56,3,4,2,15,9,11,17,18,19,22,24,26,63,28,29, 30,32,40,52,68,48,49,37,54,39,73,43,44,77,45,57,55 ,65,66,74,103,78,82,84,85,86,88,90,91,92,93,99,95, 96,97,98,100,104,105,106,107,108,109,110,111);
(B) This query takes about 1.7 seconds:

SELECT BR.REQUESTEDBY FROM BUILDREQUESTS BR WHERE BR.PROJECTID IN
(1,62,56,3,4,2,15,9,11,17,18,19,22,24,26,63,28,29, 30,32,40,52,68,48,49,37,54,39,73,43,44,77,45,57,55 ,65,66,74,103,78,82,84,85,86,88,90,91,92,93,99,95, 96,97,98,100,104,105,106,107,108,109,110,111);
Note that the only difference between Query A and Query B is the field
we are selecting. REQID and REQUESTEDBY by are both Int(11) fields. The
only difference between the two fields is that REQID is indexed. Neither
of these fields are in the WHERE clause and there is no sort order, so
what gives? Why the slowdown? Ideally, I want to select several more
fields, but this is my test case to try and optimize first.

The machine I am running on is fast, single CPU, 512M RAM. MySql 3.23,
default install via Red Hat 8. I have tried tweaking several buffer
settings in my.cnf to no avail.

What am I missing!?

Thanks,
John

Jul 19 '05 #2

The only thing I can think of so far, and I'm not sure what to do about
it, is that the table is "Dynamic", not "Fixed". Perhaps jumping around
from record to record is causing the slowdown, as there are multiple
text columns in the table. The select on reqid is fast, because it is
an indexed field and there is no need to go to the table data to get the
value. Problem is that I cannot index every field I want to display.

John
John wrote:

Hello,

I have a table called BUILDREQUESTS which I want to select from,
depending on the project ID of each record. The Project ID field is
indexed.

(A) This query runs almost instantly:

SELECT BR.REQID FROM BUILDREQUESTS BR WHERE BR.PROJECTID IN
(1,62,56,3,4,2,15,9,11,17,18,19,22,24,26,63,28,29, 30,32,40,52,68,48,49,37,54,39,73,43,44,77,45,57,55 ,65,66,74,103,78,82,84,85,86,88,90,91,92,93,99,95, 96,97,98,100,104,105,106,107,108,109,110,111);
(B) This query takes about 1.7 seconds:

SELECT BR.REQUESTEDBY FROM BUILDREQUESTS BR WHERE BR.PROJECTID IN
(1,62,56,3,4,2,15,9,11,17,18,19,22,24,26,63,28,29, 30,32,40,52,68,48,49,37,54,39,73,43,44,77,45,57,55 ,65,66,74,103,78,82,84,85,86,88,90,91,92,93,99,95, 96,97,98,100,104,105,106,107,108,109,110,111);
Note that the only difference between Query A and Query B is the field
we are selecting. REQID and REQUESTEDBY by are both Int(11) fields. The
only difference between the two fields is that REQID is indexed. Neither
of these fields are in the WHERE clause and there is no sort order, so
what gives? Why the slowdown? Ideally, I want to select several more
fields, but this is my test case to try and optimize first.

The machine I am running on is fast, single CPU, 512M RAM. MySql 3.23,
default install via Red Hat 8. I have tried tweaking several buffer
settings in my.cnf to no avail.

What am I missing!?

Thanks,
John

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Willem | last post: by
5 posts views Thread by Javier | last post: by
3 posts views Thread by John young | last post: by
6 posts views Thread by Larry R Harrison Jr | last post: by
5 posts views Thread by bobh | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.