SELECT SQL_CALC_FOUND_ROWS customers.id, customers.member_type, customers.sub_type, customers.company_logo, customers.company_name , customers.slug, customers.first_name, customers.last_name, customers.phone, customers.mobile, customers.email , customers.designation, customers.ntn, customers.website, customers.dob, customers.photo , customers.city, customers.state, customers.country, customers.status, customers.package_id, customers.package_status , customers.short_name, customers.falcon_profile_url, customers.profile_photo , is_completed, profile_access , ( (customers.company_name = 'Spinning- Murata Air Jet Spinning') * 100 + (customers.company_name LIKE 'Spinning- Murata Air Jet Spinning%') * 50 + (customers.company_name LIKE '%Spinning- Murata Air Jet Spinning%') * 20 + (search.keyword LIKE '%Spinning- Murata Air Jet Spinning%') * 5 + (search.keyword LIKE '%Spinning-%') * 2 + (search.keyword LIKE '%Murata%') * 2 + (search.keyword LIKE '%Air%') * 2 + (search.keyword LIKE '%Jet%') * 2 + (search.keyword LIKE '%Spinning%') * 2 ) AS relevance FROM customers LEFT JOIN search ON (customers.id = search.member_id) WHERE 1 AND customers.status='Active' AND customers.member_type IN('Basic', 'Standard', 'Premium', 'Premium Plus') AND customers.parent_id = 0 GROUP BY customers.id ORDER BY FIELD(customers.member_type,'Premium Plus','Premium','Standard','Basic'), relevance DESC, customers.ordering ASC LIMIT 0,20