Database Tutorials Full-text Search Query Optimization গাইড ও নোট

Full-text Search হল একটি বিশেষ ধরনের অনুসন্ধান যা টেক্সট ডেটার মধ্যে শব্দ বা প্যাটার্ন অনুসন্ধান করে। এটি সাধারণত বড় টেক্সট ফিল্ড (যেমন, লং টেক্সট বা ব্লব) থেকে দ্রুতভাবে তথ্য খুঁজে বের করার জন্য ব্যবহৃত হয়। SQL ডেটাবেসে Full-text Search কার্যকরভাবে ব্যবহার করা হয় যখন টেবিলের মধ্যে বৃহৎ পরিমাণ টেক্সট ডেটা থাকে এবং আপনি সেই ডেটাতে দ্রুত অনুসন্ধান করতে চান। যেমন, একটি ব্লগ পোস্ট, মন্তব্য, অথবা প্রোডাক্ট বর্ণনাতে শব্দ অনুসন্ধান করতে।

তবে, যখন বড় টেবিল বা বড় ডেটাসেটের উপর Full-text Search ব্যবহার করা হয়, তখন কুয়েরি পারফরম্যান্সের উন্নতির জন্য কিছু বিশেষ কৌশল অনুসরণ করা প্রয়োজন।

Full-text Search Query Optimization কৌশলগুলি:

1. Full-text Indexing ব্যবহার করা:

   Full-text Index তৈরি করা প্রথম এবং সবচেয়ে গুরুত্বপূর্ণ পদক্ষেপ। যখন আপনি একটি টেবিলের উপর Full-text search চালাতে চান, তখন আপনাকে প্রথমে ওই কলামের উপর Full-text index তৈরি করতে হবে।

   • Full-text Index ডেটাবেসকে বলে দেবে যে কোন কলামটি টেক্সট সার্চ করার জন্য উপযুক্ত, এবং এটি দ্রুত অনুসন্ধান করার জন্য সমস্ত শব্দের একটি সূচী তৈরি করবে।

   Full-text Index তৈরি:

   CREATE FULLTEXT INDEX idx_title_body
   ON posts (title, body);
   

   এখানে, posts টেবিলের title এবং body কলামের উপর Full-text index তৈরি করা হয়েছে।

2. MATCH AGAINST ব্যবহার করা:

   MATCH AGAINST হল SQL-এ Full-text Search করার জন্য ব্যবহৃত প্রধান কমান্ড। এটি শব্দ বা প্যাটার্ন অনুসন্ধান করার জন্য ইন্ডেক্স ব্যবহার করে। MATCH নির্দেশ করে যে আপনি কোন কলামে অনুসন্ধান করতে চান এবং AGAINST নির্দেশ করে যে আপনি কী শব্দ বা প্যাটার্ন অনুসন্ধান করতে চান।

   MATCH AGAINST উদাহরণ:

   SELECT * 
   FROM posts
   WHERE MATCH (title, body) AGAINST ('+SQL +optimization' IN BOOLEAN MODE);
   

   এখানে, MATCH অনুসন্ধান করবে title এবং body কলামে, এবং AGAINST নির্দেশ করবে যে এটি 'SQL' এবং 'optimization' শব্দগুলো অনুসন্ধান করবে। BOOLEAN MODE এর মাধ্যমে আপনি আরও জটিল অনুসন্ধান পরিচালনা করতে পারেন (যেমন, + শব্দের সাথে বাধ্যতামূলক থাকা, - শব্দ বাদ দেয়া ইত্যাদি)।

3. BOOLEAN MODE ব্যবহার করা:

   BOOLEAN MODE ফিচারটি Full-text search কে আরও শক্তিশালী এবং নমনীয় করে তোলে। এটি আপনাকে কুয়েরিতে বিশেষ চিহ্ন ব্যবহার করতে দেয় (যেমন, +, -, *, >), যার মাধ্যমে আপনি সার্চের শর্ত আরও সুনির্দিষ্ট বা ফিল্টার করতে পারেন।

   • + (প্লাস): শব্দটি অবশ্যই থাকতে হবে।
   • - (মাইনাস): শব্দটি থাকলে হবে না।
   • > (গ্রেটার-থান): শব্দটির গুরুত্ব বেশি।
   • < (লেস-থান): শব্দটির গুরুত্ব কম।
   • * (অ্যাস্টারিস্ক): কোনো অংশের জন্য ওয়াইল্ডকার্ড।

   BOOLEAN MODE উদাহরণ:

   SELECT * 
   FROM posts
   WHERE MATCH (title, body) AGAINST ('+database -SQL' IN BOOLEAN MODE);
   

   এই কুয়েরি database শব্দটি থাকতে হবে এবং SQL শব্দটি বাদ দিতে হবে।

4. Stop Words এবং Minimum Word Length:

   Stop words হল সাধারণ শব্দ যেমন "the", "is", "at" ইত্যাদি, যেগুলি সাধারণত Full-text search থেকে বাদ দেয়া হয়। এগুলি পারফরম্যান্সে কোনো সহায়তা দেয় না, কারণ এগুলি প্রায় প্রতিটি টেক্সট ডেটাতে থাকে। ডিফল্টভাবে, বেশ কিছু ডেটাবেসে Stop words বাদ দেয়া হয়, তবে আপনি প্রয়োজন হলে এগুলি কনফিগার করতে পারেন।

   এছাড়া, বেশ কিছু ডেটাবেসে Minimum Word Length সেটিং থাকে। সাধারণত, এটি 3 অক্ষরের কম শব্দ বাদ দেয়। আপনি যদি চাইলে এই সীমা পরিবর্তন করতে পারেন।

   Stop Words কনফিগারেশন:

   কিছু ডেটাবেসে, আপনি নিজে কাস্টম Stop Words তালিকা তৈরি করতে পারেন এবং সেটি Full-text index তৈরি করার সময় ব্যবহার করতে পারেন।

5. Query Caching এবং Index Optimization:

   Query Caching একটি গুরুত্বপূর্ণ কৌশল, যেখানে অনুসন্ধান ফলাফলগুলির একটি কপি মেমরিতে সংরক্ষণ করা হয়, যাতে পরবর্তী সময়ে একই কুয়েরি চাওয়ার সময় দ্রুত ফলাফল পাওয়া যায়।

   Index Optimization এর মাধ্যমে Full-text index পুনরায় তৈরি বা পুনরুদ্ধার করা হয়, যাতে Query Performance আরও উন্নত হয়।

6. LIMIT এবং OFFSET ব্যবহার করা:

   যখন আপনি বড় ডেটাসেটের মধ্যে Full-text Search চালাচ্ছেন, তখন LIMIT এবং OFFSET ব্যবহার করা উচিত, যাতে আপনি একবারে সমস্ত ডেটা না নিয়ে ছোট ছোট অংশে ফলাফল পাবেন। এটি সার্চের পারফরম্যান্স উন্নত করে।

   LIMIT ব্যবহার করা:

   SELECT * 
   FROM posts
   WHERE MATCH (title, body) AGAINST ('optimization')
   LIMIT 10;
   

7. Relevancy Score ব্যবহার করা:

   অনেক সময় আপনি চান যে সার্চের ফলাফলগুলি Relevancy Score এর ভিত্তিতে সাজানো হোক। এটি আপনাকে সার্চের রেজাল্টগুলির প্রাধান্য নির্ধারণ করতে সাহায্য করে, যেমন, কোন শব্দটি আরও গুরুত্বপূর্ণ বা প্রাসঙ্গিক।

   Relevancy Score উদাহরণ:

   SELECT title, body, MATCH (title, body) AGAINST ('+database +optimization' IN BOOLEAN MODE) AS relevance
   FROM posts
   ORDER BY relevance DESC;
   

   এখানে, relevance হচ্ছে একটি স্কোর যা দেখাবে কোন রেকর্ডটি আপনার অনুসন্ধানের জন্য সবচেয়ে প্রাসঙ্গিক।

উপসংহার:

Full-text Search Query Optimization বিভিন্ন কৌশল ব্যবহার করে আপনার ডেটাবেসে টেক্সট অনুসন্ধান দ্রুত এবং কার্যকরী করতে সাহায্য করে। Full-text indexing তৈরি করা, BOOLEAN MODE এর ব্যবহার, Stop Words এবং Minimum Word Length কনফিগারেশন করা, এবং Relevancy Scoring এর মাধ্যমে আপনি টেক্সট অনুসন্ধানকে আরও দ্রুত এবং কার্যকরী করতে পারেন। ডেটাবেসের পারফরম্যান্স বজায় রাখতে এই টিপসগুলি অনুসরণ করা গুরুত্বপূর্ণ।