I've been trying different options and have run into a (basic) question...I feel like I'm overlooking something too easy...
When I use phpMyAdmin to query the database (SELECT * FROM
indexedFieldID2 IN ( 1,2,3 ) AND
someField = 'X'), it says "Showing rows 0 - 29 ( 15,986 total, Query took 0.0039 sec)". Somehow it knows there were 15,986 rows (out of 2 million) that met my query criteria...and it figured that out in 0.0039 seconds. That's great!
The problem is that I need to query the 2 million rows again using an ID field in those 15,986 rows in the WHERE clause. If I return all 15,986 results from the query, the query takes far too long. Is there a way to use the info from the first query in a second query without returning it?
I thought the following INNER JOIN might work. But when I use the following, it takes 1.3 seconds which is still too long (since the query is run frequently):
SELECT * FROM
myInfo as m INNER JOIN
myInfo as e ON e.indexedFieldID1=m.parentID WHERE e.
indexedFieldID2 IN (1,2,3) AND e.
someField ='X' AND m.
The table has an index on indexedFieldID1 and indexedFieldID2.
Having given the other options a bit more thought, I think NogDog's idea would work best for my particular needs in the long-run with other peripheral activities required. Just need to find a way to improve the query times.
Is there another way (a better formed query, perhaps, or temporary table, or another aspect to databases) that would be better suited for this?
[maybe this question should be moved to the SQL section of the forum?]