Test mefe_unit_id index / look up speed

[kaihendry]

#128 (comment)

We need to go back to using a string aka UUID for uniquely identifying a record. Counter won't work.

 mefe_invitation_id	varchar(256) NULL	The unique Id for the invitation that was generated in MEFE to do the data import

time ./connect.sh <<END
> SELECT * FROM ut_invitation_api_data WHERE mefe_invitation_id = "SuhQj2FGSmzuXwgmC"\G
> END
Connecting to DEV auroradb.dev.unee-t.com
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
                          id: 1
          mefe_invitation_id: SuhQj2FGSmzuXwgmC
mefe_invitation_id_int_value: NULL
        bzfe_invitor_user_id: 6
                  bz_user_id: 112
           user_role_type_id: 5
                 is_occupant: 0
                  bz_case_id: 64772
                  bz_unit_id: 94
             invitation_type: type_cc
           is_mefe_only_user: 1
                   user_more: Use Unee-T for faster reply
        mefe_invitor_user_id: NULL
          processed_datetime: 2018-03-29 09:01:51
                      script: NULL
           api_post_datetime: 2018-03-29 09:01:51

real    0m1.151s
user    0m0.766s
sys     0m0.083s

[hendry@t480s db]$ bash explain-this.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
Bookmark lookup
+- Table
|  table          ut_invitation_api_data
|  possible_keys  mefe_invitation_id_must_be_unique
+- Constant index lookup
   key            ut_invitation_api_data->mefe_invitation_id_must_be_unique
   possible_keys  mefe_invitation_id_must_be_unique
   key_len        1027
   ref            const
   rows           1
[hendry@t480s db]$ cat explain-this.sh
MYSQL_PASSWORD=$(aws --profile uneet-dev ssm get-parameters --names MYSQL_ROOT_PASSWORD --with-decryption --query Parameters[0].Value --output text)
mysql -e 'EXPLAIN SELECT * FROM ut_invitation_api_data WHERE mefe_invitation_id = "SuhQj2FGSmzuXwgmC"' -h auroradb.dev.unee-t.com -P 3306 -u root --password=$MYSQL_PASSWORD bugzilla |
pt-visual-explain

[franck-boullier]

In the SQL the query using mefe_invitation_id has always been fast.

The issue was that the variable @mefe_invitation_id that was sent by the upstream process (Golang Script) had a different collation/charset than the field mefe_invitation_id in the database.

If we can guarantee that the variable @mefe_invitation_id is sent with the correct collation/CharSet (i.e utf8mb4 and utf8mb4_unicode_520_ci) then we know that the query will be fast.

See here for a more detailed explanation of why collation/charset are important to make sure that the SELECT is efficient (i.e. is able to use indexes).

[kaihendry]

Believe this bug is fixed now by using an UUID