we have a below json column commission in mysql table like this:

[{"lv": 0, "uid": 66, "val": 60}, {"lv": 1, "uid": 51, "val": 10}, {"lv": 2, "uid": 43, "val": 10}]

Our business logic need checl the first item’s uid.
As our data is so big, doing like this will consume serveral seconds.

Bad SQL

SELECT DISTINCT
    COUNT(1) 
FROM
    OrderItem  a
    INNER JOIN Orders b ON a.orders_id=b.id
WHERE 
    a.commission->'$[0].uid' = 111  
    AND b.user_id<> 111 AND b.status IN (1,2)

As json index is available since MySQL5.7, we did below to optimize the sql:

1. Firstly, we need a column towards commission->'$[0].uid'

   ALTER TABLE OrderItem ADD COLUMN csFirstUid bigint GENERATED ALWAYS AS ( commission->'$[0].uid' );

2. Add index for such csFirstUid column

   alter table add index idxCsFirstUid csFirstUid;

3. Change sql as following:

   SELECT DISTINCT
   COUNT(1) 
   FROM
   OrderItem  a
   INNER JOIN Orders b ON a.orders_id=b.id
   WHERE 
   a.csFirstUid = 111  
   AND b.user_id<> 111 AND b.status IN (1,2)

备注

mysql json操作

e,g: [1,2,3]
json_extract(`trackingNos`,'$[0]')  ==> trackingNos->'$[0]' ===> 1

e,g: [{"id": 1, "name": "test1"}, {"id": 2, "name": "test2"}, {"id": 3, "name": "test3"},]

json_extract(`trackingNos`,'$[1].name')  ==> trackingNos->'$[1].name' ===> "test2"