Query slower when selecting a different field

[John]

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

[John]

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

[John]

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