Found the Bottleneck…

…I think.

I did a scan of the classic ASP site (the UI for the existing EMS application) and found 1740 instances of the phrase “SELECT * FROM” in 280+ files.  1600 of those have no where clauses. 

There are over 2000 table joins across those queries, and the 140+ tables have no foreign key relationships defined.  Many don’t have primary keys, and there isn’t a clustered index in the lot. There is nary a stored proc.

Today I’ll sort through the worst offenders and try to find a way to improve performance as a band-aid solution for the next few months. I’m just hitting the most commonly used pages and using the “Slow Log” feature I found in the MySql admin console.