Physical effects of a missing index in MysQL

Last night i had one of these nights where you think about throwing the sh*** out of your window – which in this particular case would have been quite impressive as i was working on my lamps that are made out of concrete…

What happened? Well, we are close to shipping these lamps for our installation at re:publica next week in Berlin. The code on the Ardufona-Boards inside of these lamps has been tested several times and the file-name comes with the obvious “final_final_ship_real2_final” extensions already. I only wanted to add a tiny switch to every lamp to ease up the handling a little bit more. So i checked on these 29 lamps and figured out – most of them were not working properly anymore. After some investigations and test-drives i figured that they seemed to have problems with the network connection, although the LED sequences indicated everything was going well. Spent the rest of the night trying to figure out what was going on (serial console and all that shit) and went to bed angry and depressed. All the lamps should have shown the same test-color – instead they all did something different, different colors, some were off etc. – totally frustrating.

This morning the idea came to my mind to check the get-query the lamps were performing manually in the browser. Bang! Took ages to respond. Went to the source-code, grabbed the most tricky SQL-query (that proved to work fine and quick before), pasted it into the SQL-console – 2.3 seconds (i have three of them in the code)! Asked MySQL to explain the query and saw that it was not using indexes properly – which had no effect in the beginning of the project but now the data had grown a lot…

Still the question – did that cause my problem? Replaced the SQL/PHP-Script with a fake text-file including a static answer. Waited a few minutes and then went down in the cellar. For the first time in my life i saw the physical effect of a query-optimization, i was blown away:


About holadiho
This entry was posted in Allgemein. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s