Description:
I led the PHP and MySQL development for this project for SI 530, Database Design. The client was the Kelsey Museum of Archaeology at U of M, and they needed a way to browse their extensive database of over 100,000 records.
Skills used:
MySQL optimization, jQuery, PHP
Case Study
Performance issues made this project especially challenging. Totally a bit over 100,000, the record set was not overly large, but each record could have information stored for as many as 150 different fields. The client required a one-shot search feature that would search every field in every record. That’s a lot of %LIKE%’s!
Once I optimized the queries and indexed everything properly, I got the search time down to a manageable length, usually taking about 5 seconds to hit the whole record set and return the results.
This project offered other challenges, like creating a multi-field search interface that didn’t overwhelm users. Without spending much time on graphic design, we used jQuery to segment the search form into collapsible parts.
The final product was a flexible search engine and interface capable of executing general or specific-column queries. Future work may involve creating a simple CMS for uploading pictures and creating new records.