My SELECT uses 4 tables, and goes something like this:
SELECT field1, field_2 … field_20
LEFT JOIN tbl_2 on (tbl_2.field_X=tbl_1.field_A)
LEFT JOIN tbl_3 on (tbl_3.field_Y=tbl_2.field_B)
LEFT JOIN tbl_4 on (tbl_4.field_Z=tbl_3.field_C)
(tbl_1.field_3=” & val1 & “) AND
(tbl_2.field_4=” & val2 & “) AND
(tbl_2.field_5=” & val3& “) AND
(tbl_1.startDate>” & dateVal1 & “)
The above is a search query in a web application. The user has 9 search fields to pick from. Most of these fields are optional, so the WHERE could have only 1 or 2 criteria, or it could have 9.
Regarding performance and indexes: Since there are 9 search fields, do I need to create one index for every possible combination search fields used? There are too many possibilities / column combinations!! This is a search query that is performed VERY OFTEN. Updates will be performed just a few times an hour. Number of rows will grow to 10s of thousands.