Using the same select data twice in a query

Sure, the data table could look like the table below.

So, for example, I need to return all the data in rows where column A is q and column B is r.

SELECT * FROM data WHERE row IN ( SELECT row FROM data WHERE col = 'A' AND value = 'q' ) AND row IN (SELECT row FROM data WHERE col = 'B' AND value = 'r' );

Which in this case would return all columns from only row 1.

Not horrible, but a little messy.  What makes it horrible is that the data table is really created with something like

SELECT x.row, x.col, y.value FROM x INNER JOIN ( SELECT max(index), value FROM z GROUP BY value ) AS y ON x.index = y.index

which I really don't want to substitute three times into the first query.

+-----+-----+-------+
| row | col | value |
+-----+-----+-------+
|  1  |  A  |   q   |
|  1  |  B  |   r   |
|  1  |  C  |   s   |
|  2  |  A  |   q   |
|  2  |  B  |   t   |
|  2  |  C  |   u   |
+-----+-----+-------+
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

So are you suggesting I should filter in JDBC?  Technically I am still assembling the table from the recordsets, so filtering out the values wouldn't be too difficult there.

My main resistance to doing that is the assumption that MySQL, being tuned for it, would be much more efficient at stripping unwanted rows.

If you do the stripping of data through JDBC you will greatly limit the number of trips you have to make to the database, and that is what you are looking for when you are talking about performance tuning queries (At least in this case).

You would also be using client side resources to process the data, rather than server side, which will help you greatly in high traffic data environments.

Some experts might disagree with me on this seeing as how you are grabbing more data initially than you will ever need, but I think you will get much better performance filtering the data at the application level.

Well, the JDBC process is on the same machine in this case, but yeah, when performance becomes a bottleneck I could split them.  Right now the network is the tightest resource since even with filtering I will be returning 20,000 rows, ten columns.

This all may be irrelevant since preliminary benchmarks are indicating everything is too slow even without filtering.

20,000 rows and 10 columns shouldn't produce a terrible burden on resources.  Looking at what you have laid out I think that the biggest killer is the data set up.  You need to make sure that your tables are properly indexed, any field you are searching regularly the way you are  should be considered for indexing.

Proper indexes will make your data access much faster, just read up on them and make sure your indexes don't get larger than the tables themselves.

Well, looks like "You can't do that" is indeed the answer.

You've been more than patient while I waited to see if anything else would turn up, thanks.

I'll give the indexes a try, but the 1/2 second is way too long for what we need unless scaling to 1 million doesn't change that number, and I doubt indexing will give a 10x improvement.  It may be something other than a DB solution is what's needed.

You might be surprised by the performance increase with indexing.  I have seen query times go from 10 seconds to less than a second.  Give it a shot, consider any field that is being frequently accessed, especially fields that are matched by text vs.  numeric.