SELECT `al`.*, `u`.username,
MATCH (listing_title) AGAINST ('+Pablo +Picasso' IN BOOLEAN MODE) * 10 AS title_relevance,
MATCH (artist_full_name) AGAINST ('+Pablo +Picasso' IN BOOLEAN MODE) * 5 AS artist_relevance
FROM `tbl_auction_listing` AS `al`
JOIN `tbl_user` AS `u` ON `al`.`user_id` = `u`.`user_id`
WHERE 1 = 1
AND u.active > 0 AND u.removed < 1
AND `al`.`status` = 1
AND `al`.`start_date` < NOW()
AND `al`.`end_date` > NOW()
AND (
MATCH(`al`.`listing_title`) AGAINST('+Pablo +Picasso' IN BOOLEAN MODE) OR
MATCH(`al`.`artist_full_name`) AGAINST ('+Pablo +Picasso' IN BOOLEAN MODE) OR
MATCH(`al`.`item_number`) AGAINST ('Pablo Picasso*' IN BOOLEAN MODE) OR
MATCH(`al`.`description`) AGAINST ('+Pablo +Picasso' IN BOOLEAN MODE) OR
MATCH(`u`.`first_name`) AGAINST('Pablo Picasso' IN BOOLEAN MODE) OR
MATCH(`u`.`last_name`) AGAINST('Pablo Picasso' IN BOOLEAN MODE) OR
MATCH(`u`.`username`) AGAINST ('+Pablo +Picasso*' IN BOOLEAN MODE)
OR `u`.`username` = 'Pablo +Picasso'
) ORDER BY title_relevance + artist_relevance DESC
