gistfile1.sql

SELECT `Shipment`.`hwb_code`,
       CONCAT_WS(" ","HWB : ",Shipment.hwb_code,"BookingCode : ",if(Order.booking_code,Order.booking_code,"-"),"CWB : ",IF(VendorShipment.cwb_code,VendorShipment.cwb_code,"-"),"Ref : ",IF(OrderItem.reference,OrderItem.reference,"-"),"PO : ",IF(OrderItem.purchase_order,OrderItem.purchase_order,"-")) AS name
FROM `pure_kirim`.`shipments` AS `Shipment`
LEFT JOIN `pure_kirim`.`orders` AS `Order` ON (`Order`.`id` = `Shipment`.`order_id`)
LEFT JOIN `pure_kirim`.`order_items` AS `OrderItem` ON (`OrderItem`.`order_id` = `Shipment`.`order_id`)
LEFT JOIN `pure_kirim`.`vendor_shipments` AS `VendorShipment` ON (`VendorShipment`.`shipment_id` = `Shipment`.`id`)
WHERE ((`Shipment`.`hwb_code` LIKE '90%')
       OR (`OrderItem`.`purchase_order` LIKE '90%')
       OR (`OrderItem`.`reference` LIKE '90%')
       OR (`Order`.`booking_code` LIKE '90%')
       OR (`VendorShipment`.`cwb_code` LIKE '90%'))
LIMIT 50

gistfile2.sql

ALTER TABLE  `vendor_shipments` ADD INDEX (  `shipment_id` );

ALTER TABLE  `pure_kirim`.`shipments` 
  DROP INDEX  `hwb_code` ,
  ADD INDEX  `hwb_code` (  `hwb_code` ,  `order_id` );