How to run an inner join query with multiple regexp values in MySql?

Question

I have written the following query and it is working fine for a single hostname

SELECT hostname,
       orderId,
       CASE WHEN status = 30 THEN 'Finished' ELSE 'Error' END 'status',
       CASE WHEN status = 30 THEN '' ELSE Heading END 'Heading',
       CASE WHEN status = 30 THEN '' ELSE Detail END 'Detail'
FROM(SELECT host.hostname,
            cb.orderId,
            cb.status,
            cb.chefbook_name AS Heading,
            (SELECT log_message
             FROM   chefbookrun rr
             WHERE  rr.chefbook_id = c.id
             ORDER  BY id DESC 
             LIMIT 1 ) AS Detail 
     FROM host
     INNER JOIN host_infl hif ON host.vc_server_id = hif.vc_server_id
     INNER JOIN chefcookbook cb ON hif.id = cb.host_id
     WHERE  host.hostname REGEXP 'abc'
     ORDER  BY cb.orderId LIMIT 1
) AS temp;

I am getting the output as following:

hostname    orderId status     Heading          Detail
abc.com     3       Finished

I want to run the same query for multiple hostnames. I am performing the change in REGEXP as below but it is still returning the same output as above and has only a single hostname

WHERE  host.hostname REGEXP '^(abc|cde|efg)'

My desired output is as below:

    hostname    orderId status  Heading         Detail
   abc.com      3       Finished
   cde.com      3       Error   HeadingA        Details1
   efg.com      3       Error   HeadingA        Details1

Sample Data:

hostname    orderId status  Heading         Detail
abc.com     3       30      HeadingA        Details1
abc.com     5       40      HeadingB        Details2
... more rows

hostname    orderId status  Heading         Detail
cde.com     3       40      HeadingA        Details1
cde.com     5       30      HeadingB        Details2
... more rows

hostname    orderId status  Heading         Detail
efg.com     3       50      HeadingA        Details1
efg.com     5       30      HeadingB        Details2
... more rows

Sample data would make it easier to figure out the problem... — Nick, May 05 '20 at 23:40
You have a `LIMIT 1` on your `temp` derived table which is restricting you to one output row — Nick, May 05 '20 at 23:48
But I need only the topmost row for every `hostname` and that is why I have included `LIMIT 1` — meallhour, May 05 '20 at 23:51
Take a look at https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results (not the accepted answer though) — Nick, May 05 '20 at 23:53
I am looking at it and it seems I need to include `group by` clause. Can you help with on how to include `group by` and update the query? — meallhour, May 05 '20 at 23:57

Nick · Accepted Answer · 2020-05-06T00:37:28.047

1

You need to remove the LIMIT 1 from your temp derived table as that is restricting you to one output row. You can then JOIN the temp table to a derived table of minimum orderId values for each hostname to get the row with the minimum orderId for each host:

JOIN (
  SELECT hostname, MIN(cb.orderId) AS orderId
  FROM host
  INNER JOIN host_infl hif ON host.vc_server_id = hif.vc_server_id
  INNER JOIN chefcookbook cb ON hif.id = cb.host_id
  WHERE  host.hostname REGEXP 'abc'
  GROUP BY hostname
) m ON m.hostname = temp.hostname AND m.orderId = temp.orderId

edited May 06 '20 at 00:37

answered May 06 '20 at 00:03

Nick

138,499
22
57
95

I am getting an error `Unknown column 'orderId' in 'field list'`. Looks like that it is related to `MIN(orderId)` – meallhour May 06 '20 at 00:13
Please note that `orderId` is coming from the table `chefcookbook cb` and not `host`. How can I update your answer accordingly? – meallhour May 06 '20 at 00:16
@meallhour Sorry, didn't notice that - see my edit, that should include the necessary tables to get `orderId` – Nick May 06 '20 at 00:30
Thanks a lot. This query is working but it is taking 5seconds to run. I see that we are running same `INNER JOIN` multiple times. Can we update the query in some way such that it takes around 1 second to run? – meallhour May 06 '20 at 00:35
@meallhour Adding a `WHERE` clause to the subquery will help (see my edit). Also `REGEXP` is slow, you might find `WHERE hostname LIKE 'abc%' OR hostname LIKE 'cde%'...` is faster. The other thing to do is look at your `EXPLAIN` output and see what hints that might offer. – Nick May 06 '20 at 00:39
Adding `where` has reduced the time to 0.6 secs. Thanks a lot !! – meallhour May 06 '20 at 00:41

How to run an inner join query with multiple regexp values in MySql?

1 Answers1