৮ minute এর একটি query optimization এর চেষ্টা

একটা somewhat complex query optimisation এর experience ... index or not not index ?

Database এ index একধরণের data structure যেটা দিয়ে আমরা data retrieval performance অনেক improve করতে পারে । সাধারণত, যদি আমাদের whole dataset search করা লাগে । Specially larger dataset এ। সেক্ষেত্রে query time অনেক বেড়ে যেতে পারে। কারন এই algorithm এর time complexity হলো O(n), where n = length of dataset.

But what if, dataset এর length কম হতো ? তাহলে কিন্তু আবার আগের মতই সময় কম লাগতো (under a normal circumstance, for most / common scenarios).

তো, index আমাদের এসব ক্ষেত্রে অনেক help করতে পারে, আমাদের query planner and engine কে । Kinda like lookup dataset এর length কমিয়ে দেয়। আর indexing অনেক common আর অনেকের কাছে defacto db optimization.

একি সাথে, আমাদের dataset ORDER করা লাগে। আর sometimes index দিয়ে ORDER BY clause কে satisfy করা যায় । যদি query’র এমন column এর উপর data sort করা লাগে যেটার index আছে, optimizer হয়ত ঐ path choose করতে পারে । Remember, database software অনেক গুলো component এর একটা combination, আমাদের একটা simple SELECT $columns FROM $somewhere database engine এর কাছে অতটাও simple না । It goes through a lot of steps to get you the data you are looking for.

Anyways, optimizer sometimes index (pre-satisfied) sorting use করতে পারে (filesorting) rather than আলাদা ভাবে sorting step execute করে !

Having said that, index করা কি সময় সময় beneficial. Well, not really. Recently, আমাদের একটা table, যেটা আরো বেশ কয়েকটা table এর সাথে JOIN query run করে । Not the most complex query in our system. আমাদের ঐ query টা বেশ common, কয়েকটা যায়গা তে use হয়েছে ।

কয়েকদিন আগে, আমাদের একজন engineer দেখলেন, আমাদের ঐ query টা certain user এর জন্য বেশ slow হয়ে গিয়েছে । EXPLAIN করার পরে দেখা গেছে data read per join around 70 MB. So, ধরতে পারেন per (that) table access, প্রায় 70 MB এর মত data read করছে আমাদের database engine. Which is quite a lot.

এটার solution হিসাবে একটা proposal হলো NO_ORDER_INDEX, যেটা database query optimizer কে বলছে not to use a specific index (or any index) for sorting rows (i.e., for ORDER BY clauses).

এটা প্রায় ৮ min এর query কে ২~ second এ নিয়ে এসেছে ! That is pretty cool, no ? Database এর world এ, এই table টা অত বেশি rows নেই । মাত্র 50 million এর আসে পাশে । তবে, column গুলোর type, JOIN হয়া table গুলোর type আর যে join গুলো যেভাবে হচ্ছে, সেগুলো ওপর অনেক কিছু depend করে ।

এইযে optimization টা, এটার tradeoff কোথায় ? Tradeoff হলো filesort memory and disk both use করে filesort, তো এখন আমাদের db buffer pool exhausted হয়ে যাওয়ার chance অনেক বেড়ে যাচ্ছে, যদি আমরা এই solution টা adopt করি )।

Btw, এই overall database থেকে মাত্র ১০ টার মত row fetch করা হচ্ছিলো । এখানে কোনো SELECT * ছিলো না । So it’s not only about the কতটুকু data fetch করা হচ্ছে but also, কোন রাস্তায় গিয়ে করা হচ্ছে । It was about the journey, not the destination :D

Also, don’t use functions on columns where you wish to utilize the index.