Efficient Searching For Data From Large Database

[Rita Greenberg]

Hello.

We have a web based application that allows the user to search for certain records that are contained in a MySQL database. The user can enter in search criterea in any one of fifty text boxes.

Six tables need to be joined to get the final results to the user.
Currently, the search MySQL statement is built up within java code whereby each text box is checked and if the value is not spaces, the text is appended to the WHERE clause.

I need to make this more efficient since three of the six tables have millions of rows of data. I'd like to call a Stored Procedure (SP) instead of using the inline MySQL statement. I'd have to pass in 50 parameters to this SP and then build up the WHERE clause depending on the parameters that are not empty.

I also need to make sure that each of the fifty fields that can be searched on have an index associated with it.

Is there anything else I can do to speed up the data search?

Any suggestions will be greatly appreciated!

Thanks,
Rita