My SELECT uses 4 tables, and goes something like this:

SELECT field1, field_2 … field_20
FROM tbl_1
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)
WHERE
(tbl_1.status=1) AND
(tbl_1.field_3=” & val1 & “) AND
(tbl_2.field_4=” & val2 & “) AND
(tbl_2.field_5=” & val3& “) AND
(tbl_1.startDate>” & dateVal1 & “)
GROUP BY
tbl_1. field1
ORDER BY
tbl_1.startDate, tbl_1.startTime

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.

My Question:
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.

Thx!

start by creating an index on every join column that isn't already a primary key

do an EXPLAIN

then consider adding indexes on columns used in the WHERE clause (one index per column)

do an EXPLAIN

then consider adding indexes on your sort columns, or better yet, don't sort

do an EXPLAIN

finally, lose the GROUP BY clause