Hi All,
I have a requirement first to fetch top unique company_names out of duplicate values based on the product name search
and then fetch rest of the record randomly.Below is my sample table structure but the actual table has more than
90k records.
id com_id company_name product_name package_id
1 1 mufti skirt 1
2 1 mufti skirt 1
3 1 mufti pant 1
4 2 fcuk jump suit 1
5 2 fcuk shirt 1
6 2 fcuk shirt 2
7 3 denim skirt 2
8 3 denim trowser 2
9 3 denim shirt 2
10 4 flying machine skirt 2
11 4 flying machine shirt 2
12 4 flying machine pant 2
13 1 mufti skirt 1
14 1 mufti skirt 1
I tried with the below mentioned query:
SET @row_number = 0;
SELECT id, com_id, company_name,rand
FROM ( SELECT *, (@row_number := @row_number + 1) AS rand
FROM product_data AS td
WHERE MATCH (`product_name`) AGAINST ('>skirt*' IN BOOLEAN MODE) AND NOT EXISTS ( SELECT *
WHERE t.com_id=td.com_id
AND t.id=td.id- 1 ) GROUP BY company_name DESC
UNION ALL
SELECT *, ROUND(RAND() * 10, 2) + 3 AS rand
FROM product_data
WHERE MATCH (`product_name`) AGAINST ('>skirt*' IN BOOLEAN MODE) AND id NOT IN (SELECT id
FROM product_data AS td
WHERE NOT EXISTS ( SELECT id
FROM product_data AS t
WHERE t.com_id=td.com_id
AND t.id = td.id - 1 )
)
ORDER BY rand ) AS temp
To some extent its giving me the desired output but when Inline Code Example Hereit comes to large records it will take to much time.
So is there any other option to optimize it or can we randomize the record without rand()