During the last few weeks I’ve been having a lot of fun with my current project. Recently I had to invent SQL queries in order to fetch data from the database. “At the end of the day” I had a nice looking-query but it was really slow. The query was taking around 1.2 sec to execute but it was really simple and had to be executed in a production environment every 30 minutes. By our standards, we couldn’t allow such a slow query on a Magento production shop. In addition, I will say that the query was executed on a table where we had many insert and update queries while the customer was interacting with the Magento frontend.
After a short investigation I found out that adding and index to a column that I was using in the where clause reduced execution time from 1.2 sec to 0.02 sec. The good news was that the table to which I wanted to add the index was part of our own module (not part of a core or community module) and I had the freedom to add this index without doing any “harm”.
Continue reading “Magento: Add column index from upgrade scrip”