shalu2012 0 Newbie Poster

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()

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.