Indexing Really Helps

Today I needed to modify a relatively simple, single table selection [tag]query[/tag] in my web app to include [tag]left join[/tag] with another table. One table holds info about submitted reports, while the other has the results of an evaluation form for a given report. The whole application started as just a very basic online evaluation form. Then I got a request for some sort of online submission mechanism. Initially they just wanted an upload page, but I built in a tracking system to go along with it. They liked it so much that they now decided to join the two.

I needed to show which reports were evaluated and which were not. The way my tables are structured, this is essentially a basic left join. Each table has almost a thousand rows in it, so I was expecting a major slowdown. No matter what you do, that [tag]Cartesian product[/tag] necessary to do a join just kills performance-wise. I ran a test query to see exactly how painful is this going to be:

SELECT report.id, report.name, eval.id, eval.reportid
FROM report LEFT JOIN eval
ON report.id=eval.reportid

The result: 802 rows in set (13.44 sec). Painful! 13 seconds is unacceptable. People whine that the application is slow as it is.

Luckily though, I could fix this! [tag]Indexing[/tag] is your friend!

ALTER TABLE eval ADD INDEX(reportid);

I re-ran the same exact query, only to see: 802 rows in set (0.02 sec).

Wohoo! This is a world of difference! Looking at this improvement, I’m planning to carefully examine all the big queries and put indexes on pivotal columns to [tag]optimize[/tag] the db processing time. If I can shave off few seconds here and there, the overall user experience may improve.

Unfortunately the speed bump that most of my users experience is not the query time, but the crappy [tag]IE rendering engine[/tag]. It just can’t handle rendering a large [tag]HTML table[/tag] (20 columns by 50-100+ rows) without temporarily locking up the UI. Firefox users don’t even notice this issue because gecko renders the page incrementally.

Any good ideas how to optimize displaying large HTML tables in IE? I was thinking about just using span elements (or some sort of XML makup) and css to space out text on the page. It could potentially render a little bit faster than a table. Worse comes to worse, I’ll just start generating tab delimited output surrounded by <pre> tags.

I’m currently working on a feature that will dump a report into an Excel file so that they can play around with the numbers. This could be another potential solution.