Hello all....

This is a combo question regarding PHP and MySQL, but mostly lies in the MySQL realm, so I am posting this item in the Databases/SQL forum...Moderators, if you feel it's better suited in the Internet Technologies forum, please feel free to move it....

Anyway, I am grappling with a pretty complex PHP search script that queries a MySQL database....I am looking for some input on making the query more efficient and possible workarounds with URI length restrictions.

I've read the following web pages:

http://www.phpbuilder.com/...t=maximum+URL+length

and

http://www.htmlhelp.com/faq/cgifaq.2.html#9

since I think the issue lies with violating maximum URI length.

Basically, the problem is that some characters are truncated from the URI when checking multiple checkboxes...I am using GET method to pass the values for two reasons:

1) Build spanning page results
2) Emailing search results to a friend

Anyway, I have fifteen checkboxes with an average of four options per checkbox, some have 20 options. I am using the FieldNumber[] construct for the NAME of the checkboxes to translate the options selected into an array that can be used for the IN operator in the WHERE clause.

If I check a couple checkboxes, like less then 15, then the query runs and the page returns a recordset if there are any records, a No Match page if there are no records. If I check more than 15 or so checkboxes, then my browser hangs (doesn't give the 30 second timeout PHP error, just hangs).

I know that some of the issue lies with how the database is structured since the userattribute table stores both an AttributeID and valueID, rather than one foreign key. But I really can't change the structure since it is something I inherited and it would disrupt other applications in the website I am working on.

Also, the potential multiple joins between multiple tables is intensive, but it's more efficient then using (table.PK = table2.FK).

I've attached a text file to show set of PHP codes, example query output, and example URI.

Any input would be greatly appreciated.

Thanks in advance.
========================================
Buh Bye!

Cheers,
Me

Never mind...

I figured out the problem which was duplicated INNER JOINS and also switched the INNER JOINS to LEFT JOINS. The search form now processes pretty quickly...although load testing is showing some limitations in terms of number of maximum of rows, which is around 100,000 and then craps out (that's after adding additional indexes to the tables used in the query)...shouldn't be a problem in the short term, like several months to come, but in terms of scalability, the application or database would have to be re-coded in the future.
========================================
Buh Bye!

Cheers,
Me