I've distilled my problem down to this: I am trying to figure out how to build some indices on a couple of different tables so as to speed up a search that involves searching on multiple columns from each of the tables. Let's pretend I have two tables as follows and that I need to do a query that at its core basically involves a field from Table_A (Field_1) and a field from Table_B (Field_2):
+---------------+ | Table_A | +---------------+ | Primary_Key_A | | Field_1 | +---------------+ +---------------+ | Table_B | +---------------+ | Primary_Key_A | | Field_2 | +---------------+ The query that I need would look something like this: SELECT * FROM Table_A, Table_B WHERE Table_A.Primary_Key_A = Table_B.Primary_Key_A AND Field_1 IN (...) AND Field_2 IN (...); What I am curious about is this: could MySQL make use of an index that uses the primary key for the table as the first part of the index (ie: Primary_Key_A, Field_1), and would that aid in this type of query? As long as the important columns in a query are all in one table, a simple index on all of those columns makes the query lightning fast, but I am unsure of how to go about speeding up this type of query. If I only search on Field_1 (or only on Field_2), I get tens of thousands of rows returned, however the end result of searching on both Field_1 and Field_2 is usually a hundred or less rows. So let's say that MySQL has used an index on Field_2 to obtain a set of rows from Table_B. This set of rows has in turn provided MySQL with a set of values for the Primary_Key_A field which it is now about to use to continue the search on Table_A. Can MySQL make use of an index on (Primary_Key_A, Field_1) to allow it to quickly get the rows from Table_A that match? Or will it only be able to use the primary key to match the rows, and then use the where condition to check the Field_1 value for each of those rows one at a time? Even if such an index is available, will it be disregarded because the primary key is a unique key and can therefore do an equality reference instead of just a normal reference match? Thanks in advance for any insight you guys might provide me, Dan Kroymann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
