![]() Where art.Featured=0 and art.Visible=1 and c.inrecent=1 and art.Status=1 and Inner join al_categories as c on ca.CategoryID = c.CategoryID Left join al_categoryassociations as ca on art.ArticleID = ca.ArticleID Inner join al_users as u on art.AuthorID = u.UserID Select *, unix_timestamp(StartDate) as SD from al_articles as art Slow log field named sql_text showed me that doubtful query and I immediately had my enemy in front of me. Once I found potencial problem described in paragraph above I started to investigate the problematic query. Yes, you can see right, it was almost one minute to perform MySQL query! I’m sure you all agree with me, that this query time is strongly NOT acceptable and must be fixed as soon as I lose all visitors. You probably wouldn’t believe (I did not too) but number which I saw there was 00:56:14. As I mentioned earlier, I set up the long_query_time to 5 seconds but the query_time found in slow_log was pretty far from this treshold. I enabled MySQL log_slow_queries as described in my previous part of this MySQL optimization mini series.Īfter couple of hours I checked MySQL slow queries log and found very interesting entries in there. No security issues were found, so I moved into the application source code. The site was absolutely useless! First I started to investigate security of the web site and server as itself. After couple of months, when key database table had about 50.000 entries, the page loads exceded acceptable time and visitors patience. But let’s move deeper!Īt beginning the application worked fine, but along with increasing number of database (MySQL) entries, the page loading time began increase too. ![]() ![]() ![]() The bug described in this post was just one of tens found in this commercial (and not cheap!) web application. Today I knew that it was a mistake, because the time I spent correcting its issues was at least the same or very close to the time I would have spent developing an application from scratch. Primary reason was my laziness to write another application from scratch, secondary one was a time pressure. Yes, it sounds incredible, but it happened. Once upon a time I bought an application for article submissions, written in PHP. The difference between regular JOIN and optimized one is dramatic, so keep reading! This post may save your time and headaches in some MySQL problem handling. This third part of MySQL optimizing tutorial mini series shows an importance of table order during JOIN. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |