## 8.0.33 

- build used

 cat docs/INFO_SRC
commit: 74846ea20bbb7a3a236838bf49b03ce00a260b50
date: 2023-03-16 18:18:28 +0100
build-date: 2023-03-16 17:24:05 +0000
short: 74846ea20bb
branch: mysql-8.0.33-release

MySQL source 8.0.33

-- Start up (no conf provided)

rm -rf 111685/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/111685 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/111685 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/111685/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

 bin/mysql -uroot -S /tmp/mysql.sock test_db < test_db_202307080817.sql
 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT COUNT(phw.person_main_ref_id) AS count_phw_summary_like
    ->     FROM person_history_work phw
    ->     WHERE phw.work_summary LIKE '%finance%';
+------------------------+
| count_phw_summary_like |
+------------------------+
|                   5192 |
+------------------------+
1 row in set (3.40 sec)

mysql>   SELECT COUNT(phw.person_main_ref_id) AS count_phw_summmary_match
    ->     FROM person_history_work phw
    ->     WHERE MATCH(phw.work_summary) AGAINST('finance');
+--------------------------+
| count_phw_summmary_match |
+--------------------------+
|                     4841 |
+--------------------------+
1 row in set (0.01 sec)

mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_title_like
    ->     FROM person_history_work phw
    ->     WHERE phw.work_title LIKE '%software%';
+----------------------+
| count_phw_title_like |
+----------------------+
|                24031 |
+----------------------+
1 row in set (0.56 sec)

mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_title_match
    ->     FROM person_history_work phw
    ->     WHERE MATCH(phw.work_title) AGAINST ('software');
+-----------------------+
| count_phw_title_match |
+-----------------------+
|                 24018 |
+-----------------------+
1 row in set (0.02 sec)

mysql>     -- This times out at over 60000ms, should return at max 4800 records
mysql> explain     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_summary) AGAINST('finance')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_title) AGAINST('software')
    ->     );
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type     | possible_keys                                              | key                                  | key_len | ref                            | rows | filtered | Extra                                          |
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | phw   | NULL       | fulltext | fk_people_main_id_idx,IDX_person_history_work_work_summary | IDX_person_history_work_work_summary | 0       | const                          |    1 |   100.00 | Using where; Ft_hints: sorted; Start temporary |
|  1 | SIMPLE      | phw   | NULL       | fulltext | fk_people_main_id_idx,IDX_person_history_work_work_title   | IDX_person_history_work_work_title   | 0       | const                          |    1 |     5.00 | Using where; Ft_hints: no_ranking              |
|  1 | SIMPLE      | pm    | NULL       | eq_ref   | PRIMARY,ID_UNIQUE                                          | PRIMARY                              | 4       | test_db.phw.person_main_ref_id |    1 |   100.00 | Using index; End temporary                     |
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+------------------------------------------------+
3 rows in set, 1 warning (0.02 sec)

mysql>     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_summary) AGAINST('finance')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_title) AGAINST('software')
    ->     );

+--------+
| ID     |
+--------+
|  92453 |
|  92153 |
|  95871 |
|  78329 |
|  82878 |
|  92485 |
|  96393 |
|  92492 |
|   6344 |
|  44102 |
|  67879 |
|  73036 |
|  81819 |
|  88814 |
|  89154 |
|  94837 |
|  95919 |
|  95929 |
|  95930 |
| 100068 |
|  10350 |
|  19704 |
|  65412 |
|  71601 |
|  80250 |
|  83565 |
|  87882 |
|  94801 |
| 100514 |
| 112459 |
| 112711 |
| 112729 |
| 113693 |
|   2369 |
|   3020 |
|   4118 |
|   4447 |
|   4926 |
|   5625 |
|   6502 |
|   6714 |
|   7298 |
|   7339 |
|   8564 |
|   9230 |
|  11125 |
|  11582 |
|  11626 |
|  16162 |
|  19045 |
|  19385 |
|  20680 |
|  25583 |
|  26713 |
|  26721 |
|  34485 |
|  37783 |
|  38273 |
|  45178 |
|  47391 |
|  47591 |
|  48579 |
|  49152 |
|  49856 |
|  51349 |
|  59524 |
|  62771 |
|  62796 |
|  63923 |
|  64445 |
|  65054 |
|  65631 |
|  74764 |
|  74980 |
|  75768 |
|  76394 |
|  76432 |
|  76435 |
|  77556 |
|  77628 |
|  79116 |
|  80124 |
|  80229 |
|  80233 |
|  80288 |
|  81265 |
|  81367 |
|  81870 |
|  82777 |
|  82784 |
|  82802 |
|  82808 |
|  82864 |
|  82883 |
|  83619 |
|  83628 |
|  85176 |
|  90110 |
|  90934 |
|  91384 |
|  92473 |
|  92766 |
|  92790 |
|  95790 |
|  95832 |
|  95872 |
|  95915 |
|  95939 |
|  96038 |
|  96370 |
|  96378 |
|  96419 |
|  96449 |
|  96600 |
|  96607 |
|  99047 |
|  99850 |
| 100035 |
| 100055 |
| 100087 |
| 100390 |
| 100922 |
| 101000 |
| 101935 |
| 101940 |
| 101949 |
| 101961 |
| 102190 |
| 102904 |
| 104748 |
| 104759 |
| 109256 |
| 109631 |
| 112259 |
| 112568 |
| 112736 |
| 112754 |
| 113154 |
| 113223 |
| 113243 |
| 113267 |
| 113424 |
| 113434 |
| 113644 |
| 114000 |
| 114041 |
| 114068 |
| 114401 |
|    946 |
|   1983 |
|   2441 |
|   3174 |
|   3273 |
|   3847 |
|   3862 |
|   3927 |
|   4545 |
|   4550 |
|   5643 |
|   5662 |
|   5784 |
|   6713 |
|   6792 |
|   7340 |
|   7447 |
|   7476 |
|   7481 |
|   7853 |
|   8822 |
|   9153 |
|   9190 |
|   9261 |
|   9524 |
|   9658 |
|   9736 |
|   9770 |
|  10369 |
|  10371 |
|  10937 |
|  11127 |
|  11515 |
|  11620 |
|  12124 |
|  12127 |
|  12933 |
|  12937 |
|  13072 |
|  13076 |
|  14061 |
|  14074 |
|  14417 |
|  14446 |
|  14486 |
|  15600 |
|  15689 |
|  16197 |
|  16204 |
|  16216 |
|  16217 |
|  16421 |
|  16435 |
|  17203 |
|  18151 |
|  18190 |
|  19252 |
|  19285 |
|  19344 |
|  20416 |
|  20422 |
|  20595 |
|  20677 |
|  20733 |
|  20741 |
|  21160 |
|  21167 |
|  21634 |
|  21663 |
|  22647 |
|  22838 |
|  23726 |
|  24884 |
|  25640 |
|  25837 |
|  26200 |
|  26433 |
|  27197 |
|  27201 |
|  27983 |
|  29081 |
|  30383 |
|  30888 |
|  31828 |
|  32912 |
|  33789 |
|  35102 |
|  37778 |
|  38010 |
|  39995 |
|  41400 |
|  41974 |
|  42523 |
|  43591 |
|  44050 |
|  44162 |
|  44168 |
|  44194 |
|  44591 |
|  44881 |
|  44889 |
|  45507 |
|  45701 |
|  46209 |
|  46223 |
|  46913 |
|  47367 |
|  47601 |
|  48465 |
|  48571 |
|  48675 |
|  48993 |
|  49979 |
|  50278 |
|  50282 |
|  50294 |
|  50427 |
|  52071 |
|  52391 |
|  53057 |
|  54218 |
|  54308 |
|  54329 |
|  55379 |
|  55483 |
|  57089 |
|  57499 |
|  58501 |
|  58591 |
|  59533 |
|  59540 |
|  59666 |
|  61600 |
|  61623 |
|  63144 |
|  63918 |
|  64170 |
|  64205 |
|  64353 |
|  64424 |
|  64446 |
|  65407 |
|  65420 |
|  65902 |
|  65946 |
|  65981 |
|  66013 |
|  66066 |
|  67343 |
|  70102 |
|  70184 |
|  71093 |
|  71140 |
|  71473 |
|  72784 |
|  73880 |
|  74841 |
|  74863 |
|  75075 |
|  75764 |
|  75793 |
|  75994 |
|  77174 |
|  77328 |
|  78134 |
|  78242 |
|  78366 |
|  79127 |
|  79136 |
|  79181 |
|  79345 |
|  79426 |
|  79876 |
|  80204 |
|  80920 |
|  81815 |
|  82278 |
|  82727 |
|  82779 |
|  82816 |
|  82839 |
|  82852 |
|  82867 |
|  83617 |
|  84305 |
|  85155 |
|  85237 |
|  85497 |
|  85773 |
|  85839 |
|  86170 |
|  86642 |
|  86812 |
|  86843 |
|  87823 |
|  88033 |
|  88299 |
|  89178 |
|  89801 |
|  89836 |
|  89863 |
|  92475 |
|  93289 |
|  93295 |
|  93466 |
|  93914 |
|  94306 |
|  94817 |
|  94950 |
|  95936 |
|  95951 |
|  95953 |
|  95981 |
|  96178 |
|  96497 |
|  97257 |
|  98095 |
|  98354 |
| 100072 |
| 100074 |
| 100357 |
| 100388 |
| 100492 |
| 100544 |
| 100745 |
| 100929 |
| 101379 |
| 101922 |
| 101969 |
| 103711 |
| 104148 |
| 104568 |
| 104723 |
| 104788 |
| 105084 |
| 106146 |
| 107832 |
| 108577 |
| 108782 |
| 109101 |
| 109140 |
| 109229 |
| 109560 |
| 110011 |
| 110040 |
| 110488 |
| 110994 |
| 111102 |
| 111153 |
| 111341 |
| 111672 |
| 111754 |
| 111879 |
| 111891 |
| 112360 |
| 112361 |
| 112499 |
| 112619 |
| 112624 |
| 112630 |
| 112650 |
| 112656 |
| 112798 |
| 112854 |
| 112855 |
| 112896 |
| 112897 |
| 112900 |
| 112930 |
| 112932 |
| 112937 |
| 112951 |
| 112971 |
| 113038 |
| 113116 |
| 113147 |
| 113213 |
| 113298 |
| 113309 |
| 113316 |
| 113320 |
| 113326 |
| 113336 |
| 113341 |
| 113347 |
| 113381 |
| 113385 |
| 113396 |
| 113416 |
| 113426 |
| 113602 |
| 113616 |
| 113700 |
| 113723 |
| 113792 |
| 113795 |
| 113816 |
| 113882 |
| 113901 |
| 113979 |
| 114007 |
| 114052 |
| 114125 |
| 114408 |
| 114417 |
| 114718 |
| 114747 |
| 115049 |
| 115054 |
+--------+
457 rows in set (15 min 32.58 sec)

mysql>
mysql>     -- This takes 2000ms and compares roughly the same record count as MATCH.
mysql>
mysql> EXPLAIN     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_summary LIKE ('%finance%')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_title LIKE ('%software%')
    ->     );
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+----------------+--------+----------+-----------------------------+
| id | select_type  | table       | partitions | type   | possible_keys         | key                   | key_len | ref            | rows   | filtered | Extra                       |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+----------------+--------+----------+-----------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL                  | NULL                  | NULL    | NULL           |   NULL |   100.00 | NULL                        |
|  1 | SIMPLE       | pm          | NULL       | eq_ref | PRIMARY,ID_UNIQUE     | PRIMARY               | 4       | <subquery2>.ID |      1 |   100.00 | Using index                 |
|  1 | SIMPLE       | phw         | NULL       | ref    | fk_people_main_id_idx | fk_people_main_id_idx | 4       | <subquery2>.ID |      2 |    11.11 | Using where; FirstMatch(pm) |
|  2 | MATERIALIZED | phw         | NULL       | ALL    | fk_people_main_id_idx | NULL                  | NULL    | NULL           | 234260 |    11.11 | Using where                 |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+----------------+--------+----------+-----------------------------+
4 rows in set, 1 warning (0.00 sec)

mysql>     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_summary LIKE ('%finance%')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_title LIKE ('%software%')
    ->     );

+--------+
| ID     |
+--------+
|    910 |
|    946 |
|   1983 |
|   2369 |
|   2441 |
|   3020 |
|   3025 |
|   3174 |
|   3273 |
|   3847 |
|   3862 |
|   3927 |
|   4118 |
|   4175 |
|   4447 |
|   4496 |
|   4545 |
|   4550 |
|   4926 |
|   5625 |
|   5643 |
|   5662 |
|   5784 |
|   6344 |
|   6502 |
|   6713 |
|   6714 |
|   6792 |
|   7298 |
|   7339 |
|   7340 |
|   7447 |
|   7476 |
|   7481 |
|   7788 |
|   7853 |
|   8564 |
|   8812 |
|   8822 |
|   9153 |
|   9190 |
|   9230 |
|   9240 |
|   9261 |
|   9524 |
|   9658 |
|   9670 |
|   9736 |
|   9770 |
|  10350 |
|  10369 |
|  10371 |
|  10937 |
|  10962 |
|  11125 |
|  11127 |
|  11515 |
|  11582 |
|  11620 |
|  11626 |
|  12124 |
|  12127 |
|  12871 |
|  12933 |
|  12937 |
|  13072 |
|  13076 |
|  14061 |
|  14074 |
|  14371 |
|  14417 |
|  14446 |
|  14486 |
|  15600 |
|  15689 |
|  16162 |
|  16192 |
|  16197 |
|  16204 |
|  16216 |
|  16217 |
|  16412 |
|  16421 |
|  16435 |
|  17203 |
|  18151 |
|  18190 |
|  19045 |
|  19252 |
|  19285 |
|  19344 |
|  19385 |
|  19704 |
|  20416 |
|  20422 |
|  20595 |
|  20677 |
|  20680 |
|  20733 |
|  20741 |
|  21160 |
|  21167 |
|  21634 |
|  21663 |
|  22647 |
|  22838 |
|  23726 |
|  24791 |
|  24884 |
|  25583 |
|  25616 |
|  25640 |
|  25837 |
|  26200 |
|  26433 |
|  26713 |
|  26721 |
|  27197 |
|  27201 |
|  27983 |
|  29081 |
|  30383 |
|  30888 |
|  31828 |
|  32912 |
|  33789 |
|  34485 |
|  35102 |
|  37778 |
|  37783 |
|  38010 |
|  38273 |
|  39044 |
|  39045 |
|  39995 |
|  41400 |
|  41974 |
|  42523 |
|  43591 |
|  44050 |
|  44102 |
|  44162 |
|  44168 |
|  44194 |
|  44591 |
|  44881 |
|  44889 |
|  44903 |
|  45178 |
|  45507 |
|  45701 |
|  46209 |
|  46223 |
|  46913 |
|  47367 |
|  47391 |
|  47591 |
|  47601 |
|  48465 |
|  48571 |
|  48579 |
|  48675 |
|  48715 |
|  48993 |
|  49152 |
|  49856 |
|  49979 |
|  50278 |
|  50282 |
|  50294 |
|  50427 |
|  51349 |
|  52071 |
|  52391 |
|  53057 |
|  54218 |
|  54308 |
|  54329 |
|  55379 |
|  55483 |
|  57089 |
|  57234 |
|  57499 |
|  58501 |
|  58591 |
|  59524 |
|  59533 |
|  59540 |
|  59666 |
|  61600 |
|  61623 |
|  62771 |
|  62796 |
|  63144 |
|  63890 |
|  63918 |
|  63923 |
|  64170 |
|  64205 |
|  64353 |
|  64424 |
|  64445 |
|  64446 |
|  65054 |
|  65407 |
|  65412 |
|  65420 |
|  65631 |
|  65902 |
|  65946 |
|  65981 |
|  66013 |
|  66066 |
|  67343 |
|  67879 |
|  70102 |
|  70184 |
|  71093 |
|  71140 |
|  71473 |
|  71601 |
|  72784 |
|  72940 |
|  73036 |
|  73880 |
|  74764 |
|  74841 |
|  74863 |
|  74980 |
|  75075 |
|  75764 |
|  75768 |
|  75793 |
|  75994 |
|  76394 |
|  76432 |
|  76435 |
|  76481 |
|  77174 |
|  77285 |
|  77328 |
|  77556 |
|  77628 |
|  78134 |
|  78242 |
|  78329 |
|  78366 |
|  79116 |
|  79127 |
|  79136 |
|  79181 |
|  79345 |
|  79426 |
|  79876 |
|  80124 |
|  80192 |
|  80204 |
|  80229 |
|  80233 |
|  80250 |
|  80288 |
|  80920 |
|  81265 |
|  81367 |
|  81815 |
|  81819 |
|  81870 |
|  82278 |
|  82727 |
|  82777 |
|  82779 |
|  82784 |
|  82802 |
|  82808 |
|  82816 |
|  82839 |
|  82852 |
|  82864 |
|  82867 |
|  82878 |
|  82883 |
|  83565 |
|  83617 |
|  83619 |
|  83628 |
|  84305 |
|  85155 |
|  85176 |
|  85237 |
|  85497 |
|  85773 |
|  85839 |
|  86170 |
|  86642 |
|  86812 |
|  86843 |
|  87823 |
|  87882 |
|  88033 |
|  88299 |
|  88814 |
|  89154 |
|  89178 |
|  89801 |
|  89836 |
|  89863 |
|  90110 |
|  90509 |
|  90934 |
|  91384 |
|  92153 |
|  92453 |
|  92473 |
|  92475 |
|  92485 |
|  92492 |
|  92766 |
|  92790 |
|  93148 |
|  93289 |
|  93295 |
|  93466 |
|  93914 |
|  94306 |
|  94739 |
|  94801 |
|  94817 |
|  94837 |
|  94950 |
|  95790 |
|  95832 |
|  95871 |
|  95872 |
|  95915 |
|  95919 |
|  95929 |
|  95930 |
|  95936 |
|  95939 |
|  95951 |
|  95953 |
|  95981 |
|  95991 |
|  96038 |
|  96178 |
|  96370 |
|  96378 |
|  96393 |
|  96419 |
|  96449 |
|  96497 |
|  96600 |
|  96607 |
|  97257 |
|  98095 |
|  98354 |
|  99047 |
|  99850 |
| 100035 |
| 100055 |
| 100068 |
| 100072 |
| 100074 |
| 100087 |
| 100357 |
| 100388 |
| 100390 |
| 100492 |
| 100514 |
| 100544 |
| 100745 |
| 100922 |
| 100929 |
| 101000 |
| 101379 |
| 101922 |
| 101935 |
| 101940 |
| 101949 |
| 101961 |
| 101969 |
| 102190 |
| 102904 |
| 102910 |
| 103711 |
| 104148 |
| 104568 |
| 104723 |
| 104748 |
| 104759 |
| 104788 |
| 105084 |
| 106146 |
| 107285 |
| 107832 |
| 108577 |
| 108782 |
| 109101 |
| 109140 |
| 109229 |
| 109256 |
| 109560 |
| 109631 |
| 110011 |
| 110040 |
| 110488 |
| 110994 |
| 111102 |
| 111153 |
| 111341 |
| 111672 |
| 111754 |
| 111879 |
| 111891 |
| 112259 |
| 112360 |
| 112361 |
| 112459 |
| 112499 |
| 112568 |
| 112619 |
| 112624 |
| 112627 |
| 112630 |
| 112650 |
| 112656 |
| 112711 |
| 112729 |
| 112736 |
| 112745 |
| 112754 |
| 112798 |
| 112854 |
| 112855 |
| 112896 |
| 112897 |
| 112900 |
| 112930 |
| 112932 |
| 112937 |
| 112951 |
| 112971 |
| 113038 |
| 113116 |
| 113147 |
| 113154 |
| 113213 |
| 113223 |
| 113243 |
| 113267 |
| 113298 |
| 113309 |
| 113316 |
| 113320 |
| 113326 |
| 113336 |
| 113341 |
| 113347 |
| 113381 |
| 113385 |
| 113396 |
| 113416 |
| 113424 |
| 113426 |
| 113434 |
| 113602 |
| 113616 |
| 113632 |
| 113644 |
| 113693 |
| 113700 |
| 113723 |
| 113792 |
| 113795 |
| 113816 |
| 113882 |
| 113901 |
| 113976 |
| 113979 |
| 114000 |
| 114007 |
| 114041 |
| 114052 |
| 114068 |
| 114125 |
| 114401 |
| 114408 |
| 114417 |
| 114704 |
| 114718 |
| 114747 |
| 115049 |
| 115054 |
+--------+
493 rows in set (3.57 sec)


## 8.0.11(lowest checked version, first GA in 8.0), checked 8.0.11+ no change in behavior

- Modified DEFAULT for two columns as feature available only in 8.0.13+ i.e. default value specified in a DEFAULT clause can be a literal constant or an expression

diff test_db_202307080817.sql test_db_202307080817.sql.orig
49,50c49,50
<     date_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
<     date_updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
---
>     date_created datetime NOT NULL DEFAULT (NOW()),
>     date_updated datetime NOT NULL DEFAULT (NOW()) ON UPDATE CURRENT_TIMESTAMP,


-- Start up

rm -rf 111685/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/111685 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/111685 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/111685/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- build used

cat docs/INFO_SRC
commit: 4725959caed23a8f1626f5808356dd458f730e1b
date: 2018-04-08 08:17:02 +0200
build-date: 2018-04-08 08:45:05 +0200
short: 4725959
branch: mysql-8.0.11-release

MySQL source 8.0.11


 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test_db;
Query OK, 1 row affected (0.02 sec)

mysql> \q
Bye
 bin/mysql -uroot -S /tmp/mysql.sock test_db < ../mysql-8.0.33/test_db_202307080817.sql
 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_summary_like
    ->     FROM person_history_work phw
    ->     WHERE phw.work_summary LIKE '%finance%';
+------------------------+
| count_phw_summary_like |
+------------------------+
|                   5192 |
+------------------------+
1 row in set (1.81 sec)

mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_summmary_match
    ->     FROM person_history_work phw
    ->     WHERE MATCH(phw.work_summary) AGAINST('finance');
+--------------------------+
| count_phw_summmary_match |
+--------------------------+
|                     4841 |
+--------------------------+
1 row in set (0.01 sec)

mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_title_like
    ->     FROM person_history_work phw
    ->     WHERE phw.work_title LIKE '%software%';
+----------------------+
| count_phw_title_like |
+----------------------+
|                24031 |
+----------------------+
1 row in set (0.33 sec)

mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_title_match
    ->     FROM person_history_work phw
    ->     WHERE MATCH(phw.work_title) AGAINST ('software');
+-----------------------+
| count_phw_title_match |
+-----------------------+
|                 24018 |
+-----------------------+
1 row in set (0.03 sec)

mysql>
mysql>    -- This times out at over 60000ms, should return at max 4800 records
mysql> explain     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_summary) AGAINST('finance')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_title) AGAINST('software')
    ->     );
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type     | possible_keys                                              | key                                  | key_len | ref                            | rows | filtered | Extra                                          |
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | phw   | NULL       | fulltext | fk_people_main_id_idx,IDX_person_history_work_work_summary | IDX_person_history_work_work_summary | 0       | const                          |    1 |   100.00 | Using where; Ft_hints: sorted; Start temporary |
|  1 | SIMPLE      | phw   | NULL       | fulltext | fk_people_main_id_idx,IDX_person_history_work_work_title   | IDX_person_history_work_work_title   | 0       | const                          |    1 |     5.00 | Using where; Ft_hints: no_ranking              |
|  1 | SIMPLE      | pm    | NULL       | eq_ref   | PRIMARY,ID_UNIQUE                                          | PRIMARY                              | 4       | test_db.phw.person_main_ref_id |    1 |   100.00 | Using index; End temporary                     |
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+------------------------------------------------+
3 rows in set, 1 warning (0.02 sec)

mysql>     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_summary) AGAINST('finance')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_title) AGAINST('software')
    ->     );
+--------+
| ID     |
+--------+
|  92453 |
|  92153 |
|  95871 |
|  78329 |
|  82878 |
|  92485 |
|  96393 |
|  92492 |
|   6344 |
|  44102 |
|  67879 |
|  73036 |
|  81819 |
|  88814 |
|  89154 |
|  94837 |
|  95919 |
|  95929 |
|  95930 |
| 100068 |
|  10350 |
|  19704 |
|  65412 |
|  71601 |
|  80250 |
|  83565 |
|  87882 |
|  94801 |
| 100514 |
| 112459 |
| 112711 |
| 112729 |
| 113693 |
|   2369 |
|   3020 |
|   4118 |
|   4447 |
|   4926 |
|   5625 |
|   6502 |
|   6714 |
|   7298 |
|   7339 |
|   8564 |
|   9230 |
|  11125 |
|  11582 |
|  11626 |
|  16162 |
|  19045 |
|  19385 |
|  20680 |
|  25583 |
|  26713 |
|  26721 |
|  34485 |
|  37783 |
|  38273 |
|  45178 |
|  47391 |
|  47591 |
|  48579 |
|  49152 |
|  49856 |
|  51349 |
|  59524 |
|  62771 |
|  62796 |
|  63923 |
|  64445 |
|  65054 |
|  65631 |
|  74764 |
|  74980 |
|  75768 |
|  76394 |
|  76432 |
|  76435 |
|  77556 |
|  77628 |
|  79116 |
|  80124 |
|  80229 |
|  80233 |
|  80288 |
|  81265 |
|  81367 |
|  81870 |
|  82777 |
|  82784 |
|  82802 |
|  82808 |
|  82864 |
|  82883 |
|  83619 |
|  83628 |
|  85176 |
|  90110 |
|  90934 |
|  91384 |
|  92473 |
|  92766 |
|  92790 |
|  95790 |
|  95832 |
|  95872 |
|  95915 |
|  95939 |
|  96038 |
|  96370 |
|  96378 |
|  96419 |
|  96449 |
|  96600 |
|  96607 |
|  99047 |
|  99850 |
| 100035 |
| 100055 |
| 100087 |
| 100390 |
| 100922 |
| 101000 |
| 101935 |
| 101940 |
| 101949 |
| 101961 |
| 102190 |
| 102904 |
| 104748 |
| 104759 |
| 109256 |
| 109631 |
| 112259 |
| 112568 |
| 112736 |
| 112754 |
| 113154 |
| 113223 |
| 113243 |
| 113267 |
| 113424 |
| 113434 |
| 113644 |
| 114000 |
| 114041 |
| 114068 |
| 114401 |
|    946 |
|   1983 |
|   2441 |
|   3174 |
|   3273 |
|   3847 |
|   3862 |
|   3927 |
|   4545 |
|   4550 |
|   5643 |
|   5662 |
|   5784 |
|   6713 |
|   6792 |
|   7340 |
|   7447 |
|   7476 |
|   7481 |
|   7853 |
|   8822 |
|   9153 |
|   9190 |
|   9261 |
|   9524 |
|   9658 |
|   9736 |
|   9770 |
|  10369 |
|  10371 |
|  10937 |
|  11127 |
|  11515 |
|  11620 |
|  12124 |
|  12127 |
|  12933 |
|  12937 |
|  13072 |
|  13076 |
|  14061 |
|  14074 |
|  14417 |
|  14446 |
|  14486 |
|  15600 |
|  15689 |
|  16197 |
|  16204 |
|  16216 |
|  16217 |
|  16421 |
|  16435 |
|  17203 |
|  18151 |
|  18190 |
|  19252 |
|  19285 |
|  19344 |
|  20416 |
|  20422 |
|  20595 |
|  20677 |
|  20733 |
|  20741 |
|  21160 |
|  21167 |
|  21634 |
|  21663 |
|  22647 |
|  22838 |
|  23726 |
|  24884 |
|  25640 |
|  25837 |
|  26200 |
|  26433 |
|  27197 |
|  27201 |
|  27983 |
|  29081 |
|  30383 |
|  30888 |
|  31828 |
|  32912 |
|  33789 |
|  35102 |
|  37778 |
|  38010 |
|  39995 |
|  41400 |
|  41974 |
|  42523 |
|  43591 |
|  44050 |
|  44162 |
|  44168 |
|  44194 |
|  44591 |
|  44881 |
|  44889 |
|  45507 |
|  45701 |
|  46209 |
|  46223 |
|  46913 |
|  47367 |
|  47601 |
|  48465 |
|  48571 |
|  48675 |
|  48993 |
|  49979 |
|  50278 |
|  50282 |
|  50294 |
|  50427 |
|  52071 |
|  52391 |
|  53057 |
|  54218 |
|  54308 |
|  54329 |
|  55379 |
|  55483 |
|  57089 |
|  57499 |
|  58501 |
|  58591 |
|  59533 |
|  59540 |
|  59666 |
|  61600 |
|  61623 |
|  63144 |
|  63918 |
|  64170 |
|  64205 |
|  64353 |
|  64424 |
|  64446 |
|  65407 |
|  65420 |
|  65902 |
|  65946 |
|  65981 |
|  66013 |
|  66066 |
|  67343 |
|  70102 |
|  70184 |
|  71093 |
|  71140 |
|  71473 |
|  72784 |
|  73880 |
|  74841 |
|  74863 |
|  75075 |
|  75764 |
|  75793 |
|  75994 |
|  77174 |
|  77328 |
|  78134 |
|  78242 |
|  78366 |
|  79127 |
|  79136 |
|  79181 |
|  79345 |
|  79426 |
|  79876 |
|  80204 |
|  80920 |
|  81815 |
|  82278 |
|  82727 |
|  82779 |
|  82816 |
|  82839 |
|  82852 |
|  82867 |
|  83617 |
|  84305 |
|  85155 |
|  85237 |
|  85497 |
|  85773 |
|  85839 |
|  86170 |
|  86642 |
|  86812 |
|  86843 |
|  87823 |
|  88033 |
|  88299 |
|  89178 |
|  89801 |
|  89836 |
|  89863 |
|  92475 |
|  93289 |
|  93295 |
|  93466 |
|  93914 |
|  94306 |
|  94817 |
|  94950 |
|  95936 |
|  95951 |
|  95953 |
|  95981 |
|  96178 |
|  96497 |
|  97257 |
|  98095 |
|  98354 |
| 100072 |
| 100074 |
| 100357 |
| 100388 |
| 100492 |
| 100544 |
| 100745 |
| 100929 |
| 101379 |
| 101922 |
| 101969 |
| 103711 |
| 104148 |
| 104568 |
| 104723 |
| 104788 |
| 105084 |
| 106146 |
| 107832 |
| 108577 |
| 108782 |
| 109101 |
| 109140 |
| 109229 |
| 109560 |
| 110011 |
| 110040 |
| 110488 |
| 110994 |
| 111102 |
| 111153 |
| 111341 |
| 111672 |
| 111754 |
| 111879 |
| 111891 |
| 112360 |
| 112361 |
| 112499 |
| 112619 |
| 112624 |
| 112630 |
| 112650 |
| 112656 |
| 112798 |
| 112854 |
| 112855 |
| 112896 |
| 112897 |
| 112900 |
| 112930 |
| 112932 |
| 112937 |
| 112951 |
| 112971 |
| 113038 |
| 113116 |
| 113147 |
| 113213 |
| 113298 |
| 113309 |
| 113316 |
| 113320 |
| 113326 |
| 113336 |
| 113341 |
| 113347 |
| 113381 |
| 113385 |
| 113396 |
| 113416 |
| 113426 |
| 113602 |
| 113616 |
| 113700 |
| 113723 |
| 113792 |
| 113795 |
| 113816 |
| 113882 |
| 113901 |
| 113979 |
| 114007 |
| 114052 |
| 114125 |
| 114408 |
| 114417 |
| 114718 |
| 114747 |
| 115049 |
| 115054 |
+--------+
457 rows in set (14 min 17.01 sec)

mysql>     -- This takes 2000ms and compares roughly the same record count as MATCH.
mysql> explain     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_summary LIKE ('%finance%')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_title LIKE ('%software%')
    ->     );
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+----------------+--------+----------+-----------------------------+
| id | select_type  | table       | partitions | type   | possible_keys         | key                   | key_len | ref            | rows   | filtered | Extra                       |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+----------------+--------+----------+-----------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL                  | NULL                  | NULL    | NULL           |   NULL |   100.00 | NULL                        |
|  1 | SIMPLE       | pm          | NULL       | eq_ref | PRIMARY,ID_UNIQUE     | PRIMARY               | 4       | <subquery2>.ID |      1 |   100.00 | Using index                 |
|  1 | SIMPLE       | phw         | NULL       | ref    | fk_people_main_id_idx | fk_people_main_id_idx | 4       | <subquery2>.ID |      2 |    11.11 | Using where; FirstMatch(pm) |
|  2 | MATERIALIZED | phw         | NULL       | ALL    | fk_people_main_id_idx | NULL                  | NULL    | NULL           | 252184 |    11.11 | Using where                 |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+----------------+--------+----------+-----------------------------+
4 rows in set, 1 warning (0.01 sec)

mysql>     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_summary LIKE ('%finance%')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_title LIKE ('%software%')
    ->     );
+--------+
| ID     |
+--------+
|    910 |
|    946 |
|   1983 |
|   2369 |
|   2441 |
|   3020 |
|   3025 |
|   3174 |
|   3273 |
|   3847 |
|   3862 |
|   3927 |
|   4118 |
|   4175 |
|   4447 |
|   4496 |
|   4545 |
|   4550 |
|   4926 |
|   5625 |
|   5643 |
|   5662 |
|   5784 |
|   6344 |
|   6502 |
|   6713 |
|   6714 |
|   6792 |
|   7298 |
|   7339 |
|   7340 |
|   7447 |
|   7476 |
|   7481 |
|   7788 |
|   7853 |
|   8564 |
|   8812 |
|   8822 |
|   9153 |
|   9190 |
|   9230 |
|   9240 |
|   9261 |
|   9524 |
|   9658 |
|   9670 |
|   9736 |
|   9770 |
|  10350 |
|  10369 |
|  10371 |
|  10937 |
|  10962 |
|  11125 |
|  11127 |
|  11515 |
|  11582 |
|  11620 |
|  11626 |
|  12124 |
|  12127 |
|  12871 |
|  12933 |
|  12937 |
|  13072 |
|  13076 |
|  14061 |
|  14074 |
|  14371 |
|  14417 |
|  14446 |
|  14486 |
|  15600 |
|  15689 |
|  16162 |
|  16192 |
|  16197 |
|  16204 |
|  16216 |
|  16217 |
|  16412 |
|  16421 |
|  16435 |
|  17203 |
|  18151 |
|  18190 |
|  19045 |
|  19252 |
|  19285 |
|  19344 |
|  19385 |
|  19704 |
|  20416 |
|  20422 |
|  20595 |
|  20677 |
|  20680 |
|  20733 |
|  20741 |
|  21160 |
|  21167 |
|  21634 |
|  21663 |
|  22647 |
|  22838 |
|  23726 |
|  24791 |
|  24884 |
|  25583 |
|  25616 |
|  25640 |
|  25837 |
|  26200 |
|  26433 |
|  26713 |
|  26721 |
|  27197 |
|  27201 |
|  27983 |
|  29081 |
|  30383 |
|  30888 |
|  31828 |
|  32912 |
|  33789 |
|  34485 |
|  35102 |
|  37778 |
|  37783 |
|  38010 |
|  38273 |
|  39044 |
|  39045 |
|  39995 |
|  41400 |
|  41974 |
|  42523 |
|  43591 |
|  44050 |
|  44102 |
|  44162 |
|  44168 |
|  44194 |
|  44591 |
|  44881 |
|  44889 |
|  44903 |
|  45178 |
|  45507 |
|  45701 |
|  46209 |
|  46223 |
|  46913 |
|  47367 |
|  47391 |
|  47591 |
|  47601 |
|  48465 |
|  48571 |
|  48579 |
|  48675 |
|  48715 |
|  48993 |
|  49152 |
|  49856 |
|  49979 |
|  50278 |
|  50282 |
|  50294 |
|  50427 |
|  51349 |
|  52071 |
|  52391 |
|  53057 |
|  54218 |
|  54308 |
|  54329 |
|  55379 |
|  55483 |
|  57089 |
|  57234 |
|  57499 |
|  58501 |
|  58591 |
|  59524 |
|  59533 |
|  59540 |
|  59666 |
|  61600 |
|  61623 |
|  62771 |
|  62796 |
|  63144 |
|  63890 |
|  63918 |
|  63923 |
|  64170 |
|  64205 |
|  64353 |
|  64424 |
|  64445 |
|  64446 |
|  65054 |
|  65407 |
|  65412 |
|  65420 |
|  65631 |
|  65902 |
|  65946 |
|  65981 |
|  66013 |
|  66066 |
|  67343 |
|  67879 |
|  70102 |
|  70184 |
|  71093 |
|  71140 |
|  71473 |
|  71601 |
|  72784 |
|  72940 |
|  73036 |
|  73880 |
|  74764 |
|  74841 |
|  74863 |
|  74980 |
|  75075 |
|  75764 |
|  75768 |
|  75793 |
|  75994 |
|  76394 |
|  76432 |
|  76435 |
|  76481 |
|  77174 |
|  77285 |
|  77328 |
|  77556 |
|  77628 |
|  78134 |
|  78242 |
|  78329 |
|  78366 |
|  79116 |
|  79127 |
|  79136 |
|  79181 |
|  79345 |
|  79426 |
|  79876 |
|  80124 |
|  80192 |
|  80204 |
|  80229 |
|  80233 |
|  80250 |
|  80288 |
|  80920 |
|  81265 |
|  81367 |
|  81815 |
|  81819 |
|  81870 |
|  82278 |
|  82727 |
|  82777 |
|  82779 |
|  82784 |
|  82802 |
|  82808 |
|  82816 |
|  82839 |
|  82852 |
|  82864 |
|  82867 |
|  82878 |
|  82883 |
|  83565 |
|  83617 |
|  83619 |
|  83628 |
|  84305 |
|  85155 |
|  85176 |
|  85237 |
|  85497 |
|  85773 |
|  85839 |
|  86170 |
|  86642 |
|  86812 |
|  86843 |
|  87823 |
|  87882 |
|  88033 |
|  88299 |
|  88814 |
|  89154 |
|  89178 |
|  89801 |
|  89836 |
|  89863 |
|  90110 |
|  90509 |
|  90934 |
|  91384 |
|  92153 |
|  92453 |
|  92473 |
|  92475 |
|  92485 |
|  92492 |
|  92766 |
|  92790 |
|  93148 |
|  93289 |
|  93295 |
|  93466 |
|  93914 |
|  94306 |
|  94739 |
|  94801 |
|  94817 |
|  94837 |
|  94950 |
|  95790 |
|  95832 |
|  95871 |
|  95872 |
|  95915 |
|  95919 |
|  95929 |
|  95930 |
|  95936 |
|  95939 |
|  95951 |
|  95953 |
|  95981 |
|  95991 |
|  96038 |
|  96178 |
|  96370 |
|  96378 |
|  96393 |
|  96419 |
|  96449 |
|  96497 |
|  96600 |
|  96607 |
|  97257 |
|  98095 |
|  98354 |
|  99047 |
|  99850 |
| 100035 |
| 100055 |
| 100068 |
| 100072 |
| 100074 |
| 100087 |
| 100357 |
| 100388 |
| 100390 |
| 100492 |
| 100514 |
| 100544 |
| 100745 |
| 100922 |
| 100929 |
| 101000 |
| 101379 |
| 101922 |
| 101935 |
| 101940 |
| 101949 |
| 101961 |
| 101969 |
| 102190 |
| 102904 |
| 102910 |
| 103711 |
| 104148 |
| 104568 |
| 104723 |
| 104748 |
| 104759 |
| 104788 |
| 105084 |
| 106146 |
| 107285 |
| 107832 |
| 108577 |
| 108782 |
| 109101 |
| 109140 |
| 109229 |
| 109256 |
| 109560 |
| 109631 |
| 110011 |
| 110040 |
| 110488 |
| 110994 |
| 111102 |
| 111153 |
| 111341 |
| 111672 |
| 111754 |
| 111879 |
| 111891 |
| 112259 |
| 112360 |
| 112361 |
| 112459 |
| 112499 |
| 112568 |
| 112619 |
| 112624 |
| 112627 |
| 112630 |
| 112650 |
| 112656 |
| 112711 |
| 112729 |
| 112736 |
| 112745 |
| 112754 |
| 112798 |
| 112854 |
| 112855 |
| 112896 |
| 112897 |
| 112900 |
| 112930 |
| 112932 |
| 112937 |
| 112951 |
| 112971 |
| 113038 |
| 113116 |
| 113147 |
| 113154 |
| 113213 |
| 113223 |
| 113243 |
| 113267 |
| 113298 |
| 113309 |
| 113316 |
| 113320 |
| 113326 |
| 113336 |
| 113341 |
| 113347 |
| 113381 |
| 113385 |
| 113396 |
| 113416 |
| 113424 |
| 113426 |
| 113434 |
| 113602 |
| 113616 |
| 113632 |
| 113644 |
| 113693 |
| 113700 |
| 113723 |
| 113792 |
| 113795 |
| 113816 |
| 113882 |
| 113901 |
| 113976 |
| 113979 |
| 114000 |
| 114007 |
| 114041 |
| 114052 |
| 114068 |
| 114125 |
| 114401 |
| 114408 |
| 114417 |
| 114704 |
| 114718 |
| 114747 |
| 115049 |
| 115054 |
+--------+
493 rows in set (1.95 sec)

mysql>


######### 5.7.42(current GA)

- Altered table def to avoid below issue and Modified DEFAULT for two columns as feature available only in 8.0.13+ i.e. default value specified in a DEFAULT clause can be a literal constant or an expression
ERROR 3102 (HY000) at line 131: Expression of generated column 'work_title_scrubbed' contains a disallowed function.

diff test_db_202307080817.sql test_db_202307080817.sql.orig
49,50c49,50
<     date_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
<     date_updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
---
>     date_created datetime NOT NULL DEFAULT (NOW()),
>     date_updated datetime NOT NULL DEFAULT (NOW()) ON UPDATE CURRENT_TIMESTAMP,
137c137
<     work_title_scrubbed text GENERATED ALWAYS AS (`work_title`) STORED,
---
>     work_title_scrubbed text GENERATED ALWAYS AS (REGEXP_REPLACE(`work_title`, _utf8mb4 '[^0-9A-Za-z]', _utf8mb4 '')) STORED,

-- build used

cat docs/INFO_SRC
commit: 1e9235c2857b510f161e082c7f01d1dbd295249e
date: 2023-03-16 16:21:52 +0100
build-date: 2023-03-16 15:25:27 +0000
short: 1e9235c2857
branch: mysql-5.7.42-release

MySQL source 5.7.42



-- start up

rm -rf 111669/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/111669 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/111669 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/111669/log.err --log-error-verbosity=3  --secure-file-priv="" --local-infile=1 2>&1 &


 bin/mysql -uroot -S /tmp/mysql.sock test_db < ../mysql-8.0.33/test_db_202307080817.sql
 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.42 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_summary_like
    ->     FROM person_history_work phw
    ->     WHERE phw.work_summary LIKE '%finance%';
+------------------------+
| count_phw_summary_like |
+------------------------+
|                   5192 |
+------------------------+
1 row in set (1.76 sec)

mysql>
mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_summmary_match
    ->     FROM person_history_work phw
    ->     WHERE MATCH(phw.work_summary) AGAINST('finance');
+--------------------------+
| count_phw_summmary_match |
+--------------------------+
|                     4841 |
+--------------------------+
1 row in set (0.00 sec)

mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_title_like
    ->     FROM person_history_work phw
    ->     WHERE phw.work_title LIKE '%software%';
+----------------------+
| count_phw_title_like |
+----------------------+
|                24031 |
+----------------------+
1 row in set (0.53 sec)

mysql>
mysql>     SELECT COUNT(phw.person_main_ref_id) AS count_phw_title_match
    ->     FROM person_history_work phw
    ->     WHERE MATCH(phw.work_title) AGAINST ('software');
+-----------------------+
| count_phw_title_match |
+-----------------------+
|                 24018 |
+-----------------------+
1 row in set (0.01 sec)

mysql>     -- This times out at over 60000ms, should return at max 4800 records
mysql> explain     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_summary) AGAINST('finance')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_title) AGAINST('software')
    ->     );
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+--------------------------------------------------+
| id | select_type | table | partitions | type     | possible_keys                                              | key                                  | key_len | ref                            | rows | filtered | Extra                                            |
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | phw   | NULL       | fulltext | fk_people_main_id_idx,IDX_person_history_work_work_summary | IDX_person_history_work_work_summary | 0       | const                          |    1 |   100.00 | Using where; Ft_hints: sorted; Start temporary   |
|  1 | SIMPLE      | pm    | NULL       | eq_ref   | PRIMARY,ID_UNIQUE                                          | PRIMARY                              | 4       | test_db.phw.person_main_ref_id |    1 |   100.00 | Using index                                      |
|  1 | SIMPLE      | phw   | NULL       | fulltext | fk_people_main_id_idx,IDX_person_history_work_work_title   | IDX_person_history_work_work_title   | 0       | const                          |    1 |     5.00 | Using where; Ft_hints: no_ranking; End temporary |
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+--------------------------------------------------+
3 rows in set, 1 warning (0.02 sec)

mysql>     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_summary) AGAINST('finance')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_title) AGAINST('software')
    ->     );

+--------+
| ID     |
+--------+
|  92453 |
|  92153 |
|  95871 |
|  78329 |
|  82878 |
|  92485 |
|  96393 |
|  92492 |
|   6344 |
|  44102 |
|  67879 |
|  73036 |
|  81819 |
|  88814 |
|  89154 |
|  94837 |
|  95919 |
|  95929 |
|  95930 |
| 100068 |
|  10350 |
|  19704 |
|  65412 |
|  71601 |
|  80250 |
|  83565 |
|  87882 |
|  94801 |
| 100514 |
| 112459 |
| 112711 |
| 112729 |
| 113693 |
|   2369 |
|   3020 |
|   4118 |
|   4447 |
|   4926 |
|   5625 |
|   6502 |
|   6714 |
|   7298 |
|   7339 |
|   8564 |
|   9230 |
|  11125 |
|  11582 |
|  11626 |
|  16162 |
|  19045 |
|  19385 |
|  20680 |
|  25583 |
|  26713 |
|  26721 |
|  34485 |
|  37783 |
|  38273 |
|  45178 |
|  47391 |
|  47591 |
|  48579 |
|  49152 |
|  49856 |
|  51349 |
|  59524 |
|  62771 |
|  62796 |
|  63923 |
|  64445 |
|  65054 |
|  65631 |
|  74764 |
|  74980 |
|  75768 |
|  76394 |
|  76432 |
|  76435 |
|  77556 |
|  77628 |
|  79116 |
|  80124 |
|  80229 |
|  80233 |
|  80288 |
|  81265 |
|  81367 |
|  81870 |
|  82777 |
|  82784 |
|  82802 |
|  82808 |
|  82864 |
|  82883 |
|  83619 |
|  83628 |
|  85176 |
|  90110 |
|  90934 |
|  91384 |
|  92473 |
|  92766 |
|  92790 |
|  95790 |
|  95832 |
|  95872 |
|  95915 |
|  95939 |
|  96038 |
|  96370 |
|  96378 |
|  96419 |
|  96449 |
|  96600 |
|  96607 |
|  99047 |
|  99850 |
| 100035 |
| 100055 |
| 100087 |
| 100390 |
| 100922 |
| 101000 |
| 101935 |
| 101940 |
| 101949 |
| 101961 |
| 102190 |
| 102904 |
| 104748 |
| 104759 |
| 109256 |
| 109631 |
| 112259 |
| 112568 |
| 112736 |
| 112754 |
| 113154 |
| 113223 |
| 113243 |
| 113267 |
| 113424 |
| 113434 |
| 113644 |
| 114000 |
| 114041 |
| 114068 |
| 114401 |
|    946 |
|   1983 |
|   2441 |
|   3174 |
|   3273 |
|   3847 |
|   3862 |
|   3927 |
|   4545 |
|   4550 |
|   5643 |
|   5662 |
|   5784 |
|   6713 |
|   6792 |
|   7340 |
|   7447 |
|   7476 |
|   7481 |
|   7853 |
|   8822 |
|   9153 |
|   9190 |
|   9261 |
|   9524 |
|   9658 |
|   9736 |
|   9770 |
|  10369 |
|  10371 |
|  10937 |
|  11127 |
|  11515 |
|  11620 |
|  12124 |
|  12127 |
|  12933 |
|  12937 |
|  13072 |
|  13076 |
|  14061 |
|  14074 |
|  14417 |
|  14446 |
|  14486 |
|  15600 |
|  15689 |
|  16197 |
|  16204 |
|  16216 |
|  16217 |
|  16421 |
|  16435 |
|  17203 |
|  18151 |
|  18190 |
|  19252 |
|  19285 |
|  19344 |
|  20416 |
|  20422 |
|  20595 |
|  20677 |
|  20733 |
|  20741 |
|  21160 |
|  21167 |
|  21634 |
|  21663 |
|  22647 |
|  22838 |
|  23726 |
|  24884 |
|  25640 |
|  25837 |
|  26200 |
|  26433 |
|  27197 |
|  27201 |
|  27983 |
|  29081 |
|  30383 |
|  30888 |
|  31828 |
|  32912 |
|  33789 |
|  35102 |
|  37778 |
|  38010 |
|  39995 |
|  41400 |
|  41974 |
|  42523 |
|  43591 |
|  44050 |
|  44162 |
|  44168 |
|  44194 |
|  44591 |
|  44881 |
|  44889 |
|  45507 |
|  45701 |
|  46209 |
|  46223 |
|  46913 |
|  47367 |
|  47601 |
|  48465 |
|  48571 |
|  48675 |
|  48993 |
|  49979 |
|  50278 |
|  50282 |
|  50294 |
|  50427 |
|  52071 |
|  52391 |
|  53057 |
|  54218 |
|  54308 |
|  54329 |
|  55379 |
|  55483 |
|  57089 |
|  57499 |
|  58501 |
|  58591 |
|  59533 |
|  59540 |
|  59666 |
|  61600 |
|  61623 |
|  63144 |
|  63918 |
|  64170 |
|  64205 |
|  64353 |
|  64424 |
|  64446 |
|  65407 |
|  65420 |
|  65902 |
|  65946 |
|  65981 |
|  66013 |
|  66066 |
|  67343 |
|  70102 |
|  70184 |
|  71093 |
|  71140 |
|  71473 |
|  72784 |
|  73880 |
|  74841 |
|  74863 |
|  75075 |
|  75764 |
|  75793 |
|  75994 |
|  77174 |
|  77328 |
|  78134 |
|  78242 |
|  78366 |
|  79127 |
|  79136 |
|  79181 |
|  79345 |
|  79426 |
|  79876 |
|  80204 |
|  80920 |
|  81815 |
|  82278 |
|  82727 |
|  82779 |
|  82816 |
|  82839 |
|  82852 |
|  82867 |
|  83617 |
|  84305 |
|  85155 |
|  85237 |
|  85497 |
|  85773 |
|  85839 |
|  86170 |
|  86642 |
|  86812 |
|  86843 |
|  87823 |
|  88033 |
|  88299 |
|  89178 |
|  89801 |
|  89836 |
|  89863 |
|  92475 |
|  93289 |
|  93295 |
|  93466 |
|  93914 |
|  94306 |
|  94817 |
|  94950 |
|  95936 |
|  95951 |
|  95953 |
|  95981 |
|  96178 |
|  96497 |
|  97257 |
|  98095 |
|  98354 |
| 100072 |
| 100074 |
| 100357 |
| 100388 |
| 100492 |
| 100544 |
| 100745 |
| 100929 |
| 101379 |
| 101922 |
| 101969 |
| 103711 |
| 104148 |
| 104568 |
| 104723 |
| 104788 |
| 105084 |
| 106146 |
| 107832 |
| 108577 |
| 108782 |
| 109101 |
| 109140 |
| 109229 |
| 109560 |
| 110011 |
| 110040 |
| 110488 |
| 110994 |
| 111102 |
| 111153 |
| 111341 |
| 111672 |
| 111754 |
| 111879 |
| 111891 |
| 112360 |
| 112361 |
| 112499 |
| 112619 |
| 112624 |
| 112630 |
| 112650 |
| 112656 |
| 112798 |
| 112854 |
| 112855 |
| 112896 |
| 112897 |
| 112900 |
| 112930 |
| 112932 |
| 112937 |
| 112951 |
| 112971 |
| 113038 |
| 113116 |
| 113147 |
| 113213 |
| 113298 |
| 113309 |
| 113316 |
| 113320 |
| 113326 |
| 113336 |
| 113341 |
| 113347 |
| 113381 |
| 113385 |
| 113396 |
| 113416 |
| 113426 |
| 113602 |
| 113616 |
| 113700 |
| 113723 |
| 113792 |
| 113795 |
| 113816 |
| 113882 |
| 113901 |
| 113979 |
| 114007 |
| 114052 |
| 114125 |
| 114408 |
| 114417 |
| 114718 |
| 114747 |
| 115049 |
| 115054 |
+--------+
457 rows in set (14 min 59.17 sec)

mysql>
mysql>    -- This takes 2000ms and compares roughly the same record count as MATCH.
mysql> explain     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_summary LIKE ('%finance%')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_title LIKE ('%software%')
    ->     );
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+----------------+--------+----------+-----------------------------+
| id | select_type  | table       | partitions | type   | possible_keys         | key                   | key_len | ref            | rows   | filtered | Extra                       |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+----------------+--------+----------+-----------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL                  | NULL                  | NULL    | NULL           |   NULL |   100.00 | NULL                        |
|  1 | SIMPLE       | pm          | NULL       | eq_ref | PRIMARY,ID_UNIQUE     | PRIMARY               | 4       | <subquery2>.ID |      1 |   100.00 | Using index                 |
|  1 | SIMPLE       | phw         | NULL       | ref    | fk_people_main_id_idx | fk_people_main_id_idx | 4       | <subquery2>.ID |      5 |    11.11 | Using where; FirstMatch(pm) |
|  2 | MATERIALIZED | phw         | NULL       | ALL    | fk_people_main_id_idx | NULL                  | NULL    | NULL           | 393380 |    11.11 | Using where                 |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+----------------+--------+----------+-----------------------------+
4 rows in set, 1 warning (0.00 sec)

mysql>     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_summary LIKE ('%finance%')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_title LIKE ('%software%')
    ->     );
+--------+
| ID     |
+--------+
|    910 |
|    946 |
|   1983 |
|   2369 |
|   2441 |
|   3020 |
|   3025 |
|   3174 |
|   3273 |
|   3847 |
|   3862 |
|   3927 |
|   4118 |
|   4175 |
|   4447 |
|   4496 |
|   4545 |
|   4550 |
|   4926 |
|   5625 |
|   5643 |
|   5662 |
|   5784 |
|   6344 |
|   6502 |
|   6713 |
|   6714 |
|   6792 |
|   7298 |
|   7339 |
|   7340 |
|   7447 |
|   7476 |
|   7481 |
|   7788 |
|   7853 |
|   8564 |
|   8812 |
|   8822 |
|   9153 |
|   9190 |
|   9230 |
|   9240 |
|   9261 |
|   9524 |
|   9658 |
|   9670 |
|   9736 |
|   9770 |
|  10350 |
|  10369 |
|  10371 |
|  10937 |
|  10962 |
|  11125 |
|  11127 |
|  11515 |
|  11582 |
|  11620 |
|  11626 |
|  12124 |
|  12127 |
|  12871 |
|  12933 |
|  12937 |
|  13072 |
|  13076 |
|  14061 |
|  14074 |
|  14371 |
|  14417 |
|  14446 |
|  14486 |
|  15600 |
|  15689 |
|  16162 |
|  16192 |
|  16197 |
|  16204 |
|  16216 |
|  16217 |
|  16412 |
|  16421 |
|  16435 |
|  17203 |
|  18151 |
|  18190 |
|  19045 |
|  19252 |
|  19285 |
|  19344 |
|  19385 |
|  19704 |
|  20416 |
|  20422 |
|  20595 |
|  20677 |
|  20680 |
|  20733 |
|  20741 |
|  21160 |
|  21167 |
|  21634 |
|  21663 |
|  22647 |
|  22838 |
|  23726 |
|  24791 |
|  24884 |
|  25583 |
|  25616 |
|  25640 |
|  25837 |
|  26200 |
|  26433 |
|  26713 |
|  26721 |
|  27197 |
|  27201 |
|  27983 |
|  29081 |
|  30383 |
|  30888 |
|  31828 |
|  32912 |
|  33789 |
|  34485 |
|  35102 |
|  37778 |
|  37783 |
|  38010 |
|  38273 |
|  39044 |
|  39045 |
|  39995 |
|  41400 |
|  41974 |
|  42523 |
|  43591 |
|  44050 |
|  44102 |
|  44162 |
|  44168 |
|  44194 |
|  44591 |
|  44881 |
|  44889 |
|  44903 |
|  45178 |
|  45507 |
|  45701 |
|  46209 |
|  46223 |
|  46913 |
|  47367 |
|  47391 |
|  47591 |
|  47601 |
|  48465 |
|  48571 |
|  48579 |
|  48675 |
|  48715 |
|  48993 |
|  49152 |
|  49856 |
|  49979 |
|  50278 |
|  50282 |
|  50294 |
|  50427 |
|  51349 |
|  52071 |
|  52391 |
|  53057 |
|  54218 |
|  54308 |
|  54329 |
|  55379 |
|  55483 |
|  57089 |
|  57234 |
|  57499 |
|  58501 |
|  58591 |
|  59524 |
|  59533 |
|  59540 |
|  59666 |
|  61600 |
|  61623 |
|  62771 |
|  62796 |
|  63144 |
|  63890 |
|  63918 |
|  63923 |
|  64170 |
|  64205 |
|  64353 |
|  64424 |
|  64445 |
|  64446 |
|  65054 |
|  65407 |
|  65412 |
|  65420 |
|  65631 |
|  65902 |
|  65946 |
|  65981 |
|  66013 |
|  66066 |
|  67343 |
|  67879 |
|  70102 |
|  70184 |
|  71093 |
|  71140 |
|  71473 |
|  71601 |
|  72784 |
|  72940 |
|  73036 |
|  73880 |
|  74764 |
|  74841 |
|  74863 |
|  74980 |
|  75075 |
|  75764 |
|  75768 |
|  75793 |
|  75994 |
|  76394 |
|  76432 |
|  76435 |
|  76481 |
|  77174 |
|  77285 |
|  77328 |
|  77556 |
|  77628 |
|  78134 |
|  78242 |
|  78329 |
|  78366 |
|  79116 |
|  79127 |
|  79136 |
|  79181 |
|  79345 |
|  79426 |
|  79876 |
|  80124 |
|  80192 |
|  80204 |
|  80229 |
|  80233 |
|  80250 |
|  80288 |
|  80920 |
|  81265 |
|  81367 |
|  81815 |
|  81819 |
|  81870 |
|  82278 |
|  82727 |
|  82777 |
|  82779 |
|  82784 |
|  82802 |
|  82808 |
|  82816 |
|  82839 |
|  82852 |
|  82864 |
|  82867 |
|  82878 |
|  82883 |
|  83565 |
|  83617 |
|  83619 |
|  83628 |
|  84305 |
|  85155 |
|  85176 |
|  85237 |
|  85497 |
|  85773 |
|  85839 |
|  86170 |
|  86642 |
|  86812 |
|  86843 |
|  87823 |
|  87882 |
|  88033 |
|  88299 |
|  88814 |
|  89154 |
|  89178 |
|  89801 |
|  89836 |
|  89863 |
|  90110 |
|  90509 |
|  90934 |
|  91384 |
|  92153 |
|  92453 |
|  92473 |
|  92475 |
|  92485 |
|  92492 |
|  92766 |
|  92790 |
|  93148 |
|  93289 |
|  93295 |
|  93466 |
|  93914 |
|  94306 |
|  94739 |
|  94801 |
|  94817 |
|  94837 |
|  94950 |
|  95790 |
|  95832 |
|  95871 |
|  95872 |
|  95915 |
|  95919 |
|  95929 |
|  95930 |
|  95936 |
|  95939 |
|  95951 |
|  95953 |
|  95981 |
|  95991 |
|  96038 |
|  96178 |
|  96370 |
|  96378 |
|  96393 |
|  96419 |
|  96449 |
|  96497 |
|  96600 |
|  96607 |
|  97257 |
|  98095 |
|  98354 |
|  99047 |
|  99850 |
| 100035 |
| 100055 |
| 100068 |
| 100072 |
| 100074 |
| 100087 |
| 100357 |
| 100388 |
| 100390 |
| 100492 |
| 100514 |
| 100544 |
| 100745 |
| 100922 |
| 100929 |
| 101000 |
| 101379 |
| 101922 |
| 101935 |
| 101940 |
| 101949 |
| 101961 |
| 101969 |
| 102190 |
| 102904 |
| 102910 |
| 103711 |
| 104148 |
| 104568 |
| 104723 |
| 104748 |
| 104759 |
| 104788 |
| 105084 |
| 106146 |
| 107285 |
| 107832 |
| 108577 |
| 108782 |
| 109101 |
| 109140 |
| 109229 |
| 109256 |
| 109560 |
| 109631 |
| 110011 |
| 110040 |
| 110488 |
| 110994 |
| 111102 |
| 111153 |
| 111341 |
| 111672 |
| 111754 |
| 111879 |
| 111891 |
| 112259 |
| 112360 |
| 112361 |
| 112459 |
| 112499 |
| 112568 |
| 112619 |
| 112624 |
| 112627 |
| 112630 |
| 112650 |
| 112656 |
| 112711 |
| 112729 |
| 112736 |
| 112745 |
| 112754 |
| 112798 |
| 112854 |
| 112855 |
| 112896 |
| 112897 |
| 112900 |
| 112930 |
| 112932 |
| 112937 |
| 112951 |
| 112971 |
| 113038 |
| 113116 |
| 113147 |
| 113154 |
| 113213 |
| 113223 |
| 113243 |
| 113267 |
| 113298 |
| 113309 |
| 113316 |
| 113320 |
| 113326 |
| 113336 |
| 113341 |
| 113347 |
| 113381 |
| 113385 |
| 113396 |
| 113416 |
| 113424 |
| 113426 |
| 113434 |
| 113602 |
| 113616 |
| 113632 |
| 113644 |
| 113693 |
| 113700 |
| 113723 |
| 113792 |
| 113795 |
| 113816 |
| 113882 |
| 113901 |
| 113976 |
| 113979 |
| 114000 |
| 114007 |
| 114041 |
| 114052 |
| 114068 |
| 114125 |
| 114401 |
| 114408 |
| 114417 |
| 114704 |
| 114718 |
| 114747 |
| 115049 |
| 115054 |
+--------+
493 rows in set (1.87 sec)

mysql>



################ With workaround set optimizer_switch="semijoin=off";


 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set optimizer_switch="semijoin=off";
Query OK, 0 rows affected (0.00 sec)

mysql>     -- This times out at over 60000ms, should return at max 4800 records
mysql> explain     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_summary) AGAINST('finance')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_title) AGAINST('software')
    ->     );
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+-------+--------+----------+-------------------------------+
| id | select_type | table | partitions | type     | possible_keys                                              | key                                  | key_len | ref   | rows   | filtered | Extra                         |
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+-------+--------+----------+-------------------------------+
|  1 | PRIMARY     | pm    | NULL       | index    | NULL                                                       | IDX_person_main_department_type_gc   | 3       | NULL  | 114906 |   100.00 | Using where; Using index      |
|  3 | SUBQUERY    | phw   | NULL       | fulltext | fk_people_main_id_idx,IDX_person_history_work_work_title   | IDX_person_history_work_work_title   | 0       | const |      1 |   100.00 | Using where; Ft_hints: sorted |
|  2 | SUBQUERY    | phw   | NULL       | fulltext | fk_people_main_id_idx,IDX_person_history_work_work_summary | IDX_person_history_work_work_summary | 0       | const |      1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+-------+------------+----------+------------------------------------------------------------+--------------------------------------+---------+-------+--------+----------+-------------------------------+
3 rows in set, 1 warning (0.04 sec)

mysql>     SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_summary) AGAINST('finance')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE MATCH(phw.work_title) AGAINST('software')
    ->     );
+--------+
| ID     |
+--------+
|   4926 |
|   8822 |
|   9524 |
|  14417 |
|  14446 |
|  14486 |
|  16217 |
|  21663 |
|  30383 |
|  38010 |
|  57089 |
|  70102 |
|  70184 |
|  72784 |
| 104148 |
| 104723 |
| 109229 |
| 110040 |
| 111891 |
|    946 |
|   1983 |
|   2369 |
|   2441 |
|   3020 |
|   3174 |
|   3273 |
|   3847 |
|   3862 |
|   3927 |
|   4118 |
|   4447 |
|   4545 |
|   4550 |
|   5625 |
|   5643 |
|   5662 |
|   5784 |
|   6344 |
|   6502 |
|   6713 |
|   6714 |
|   6792 |
|   7298 |
|   7339 |
|   7340 |
|   7447 |
|   7476 |
|   7481 |
|   7853 |
|   8564 |
|   9153 |
|   9190 |
|   9230 |
|   9261 |
|   9658 |
|   9736 |
|   9770 |
|  10350 |
|  10369 |
|  10371 |
|  10937 |
|  11125 |
|  11127 |
|  11515 |
|  11582 |
|  11620 |
|  11626 |
|  12124 |
|  12127 |
|  12933 |
|  12937 |
|  13072 |
|  13076 |
|  14061 |
|  14074 |
|  15600 |
|  15689 |
|  16162 |
|  16197 |
|  16204 |
|  16216 |
|  16421 |
|  16435 |
|  17203 |
|  18151 |
|  18190 |
|  19045 |
|  19252 |
|  19285 |
|  19344 |
|  19385 |
|  19704 |
|  20416 |
|  20422 |
|  20595 |
|  20677 |
|  20680 |
|  20733 |
|  20741 |
|  21160 |
|  21167 |
|  21634 |
|  22647 |
|  22838 |
|  23726 |
|  24884 |
|  25583 |
|  25640 |
|  25837 |
|  26200 |
|  26433 |
|  26713 |
|  26721 |
|  27197 |
|  27201 |
|  27983 |
|  29081 |
|  30888 |
|  31828 |
|  32912 |
|  33789 |
|  34485 |
|  35102 |
|  37778 |
|  37783 |
|  38273 |
|  39995 |
|  41400 |
|  41974 |
|  42523 |
|  43591 |
|  44050 |
|  44102 |
|  44162 |
|  44168 |
|  44194 |
|  44591 |
|  44881 |
|  44889 |
|  45178 |
|  45507 |
|  45701 |
|  46209 |
|  46223 |
|  46913 |
|  47367 |
|  47391 |
|  47591 |
|  47601 |
|  48465 |
|  48571 |
|  48579 |
|  48675 |
|  48993 |
|  49152 |
|  49856 |
|  49979 |
|  50278 |
|  50282 |
|  50294 |
|  50427 |
|  51349 |
|  52071 |
|  52391 |
|  53057 |
|  54218 |
|  54308 |
|  54329 |
|  55379 |
|  55483 |
|  57499 |
|  58501 |
|  58591 |
|  59524 |
|  59533 |
|  59540 |
|  59666 |
|  61600 |
|  61623 |
|  62771 |
|  62796 |
|  63144 |
|  63918 |
|  63923 |
|  64170 |
|  64205 |
|  64353 |
|  64424 |
|  64445 |
|  64446 |
|  65054 |
|  65407 |
|  65412 |
|  65420 |
|  65631 |
|  65902 |
|  65946 |
|  65981 |
|  66013 |
|  66066 |
|  67343 |
|  67879 |
|  71093 |
|  71140 |
|  71473 |
|  71601 |
|  73036 |
|  74764 |
|  74841 |
|  74863 |
|  74980 |
|  75075 |
|  75764 |
|  75768 |
|  75793 |
|  75994 |
|  76394 |
|  76432 |
|  76435 |
|  77174 |
|  77328 |
|  77556 |
|  77628 |
|  78134 |
|  78242 |
|  78329 |
|  78366 |
|  79116 |
|  79127 |
|  79136 |
|  79181 |
|  79426 |
|  80124 |
|  80204 |
|  80229 |
|  80233 |
|  80250 |
|  80288 |
|  80920 |
|  81265 |
|  81367 |
|  81815 |
|  81819 |
|  81870 |
|  82278 |
|  82727 |
|  82777 |
|  82779 |
|  82784 |
|  82802 |
|  82808 |
|  82816 |
|  82839 |
|  82852 |
|  82864 |
|  82867 |
|  82878 |
|  82883 |
|  83565 |
|  83617 |
|  83619 |
|  83628 |
|  84305 |
|  85155 |
|  85176 |
|  85237 |
|  85497 |
|  85773 |
|  85839 |
|  86170 |
|  86642 |
|  86812 |
|  86843 |
|  87823 |
|  88033 |
|  88299 |
|  88814 |
|  89154 |
|  89178 |
|  89801 |
|  89863 |
|  90110 |
|  90934 |
|  91384 |
|  92153 |
|  92453 |
|  92473 |
|  92475 |
|  92485 |
|  92492 |
|  92766 |
|  92790 |
|  93289 |
|  93295 |
|  93466 |
|  93914 |
|  94306 |
|  94801 |
|  94817 |
|  94837 |
|  94950 |
|  95790 |
|  95915 |
|  95919 |
|  95929 |
|  95930 |
|  95936 |
|  95939 |
|  95951 |
|  95953 |
|  95981 |
|  96038 |
|  96178 |
|  96370 |
|  96378 |
|  96393 |
|  96419 |
|  96449 |
|  96600 |
|  96607 |
|  97257 |
|  98095 |
|  99047 |
|  99850 |
| 100035 |
| 100055 |
| 100068 |
| 100072 |
| 100074 |
| 100087 |
| 100492 |
| 100514 |
| 100544 |
| 100745 |
| 100922 |
| 100929 |
| 101000 |
| 101379 |
| 101922 |
| 101935 |
| 101940 |
| 101949 |
| 101961 |
| 101969 |
| 102190 |
| 104568 |
| 104748 |
| 104759 |
| 104788 |
| 105084 |
| 108577 |
| 108782 |
| 109101 |
| 109140 |
| 109256 |
| 109560 |
| 109631 |
| 110488 |
| 110994 |
| 111102 |
| 111153 |
| 111341 |
| 111879 |
| 112259 |
| 112360 |
| 112361 |
| 112459 |
| 112499 |
| 112568 |
| 112619 |
| 112624 |
| 112630 |
| 112650 |
| 112656 |
| 112711 |
| 112729 |
| 112736 |
| 112754 |
| 112798 |
| 112854 |
| 112855 |
| 112896 |
| 112897 |
| 112900 |
| 112930 |
| 112932 |
| 112937 |
| 112951 |
| 112971 |
| 113038 |
| 113116 |
| 113147 |
| 113154 |
| 113213 |
| 113223 |
| 113243 |
| 113267 |
| 113298 |
| 113309 |
| 113316 |
| 113320 |
| 113326 |
| 113336 |
| 113341 |
| 113347 |
| 113381 |
| 113385 |
| 113396 |
| 113416 |
| 113424 |
| 113426 |
| 113434 |
| 113602 |
| 113616 |
| 113644 |
| 113693 |
| 113700 |
| 113723 |
| 113792 |
| 113795 |
| 113816 |
| 113882 |
| 113901 |
| 113979 |
| 114000 |
| 114007 |
| 114041 |
| 114052 |
| 114068 |
| 114125 |
| 114401 |
| 114408 |
| 114417 |
| 114718 |
| 114747 |
| 115049 |
| 115054 |
|  73880 |
|  79345 |
|  79876 |
|  89836 |
|  95832 |
|  95871 |
|  95872 |
|  96497 |
|  98354 |
| 100357 |
| 100388 |
| 100390 |
| 103711 |
| 106146 |
| 107832 |
| 110011 |
|  87882 |
| 102904 |
| 111672 |
| 111754 |
+--------+
457 rows in set (0.30 sec)

-- no changes to below query though

mysql>     -- This takes 2000ms and compares roughly the same record count as MATCH.
mysql>    SELECT pm.ID
    ->     FROM person_main pm
    ->     WHERE
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_summary LIKE ('%finance%')
    ->     )
    ->     AND
    ->     pm.ID IN
    ->     (
    ->         SELECT phw.person_main_ref_id AS ID
    ->         FROM person_history_work phw
    ->         WHERE phw.work_title LIKE ('%software%')
    ->     );
+--------+
| ID     |
+--------+
|   4926 |
|   8812 |
|   8822 |
|   9524 |
|  14417 |
|  14446 |
|  14486 |
|  16192 |
|  16217 |
|  21663 |
|  30383 |
|  38010 |
|  57089 |
|  70102 |
|  70184 |
|  72784 |
| 104148 |
| 104723 |
| 109229 |
| 110040 |
| 111891 |
|    910 |
|    946 |
|   1983 |
|   2369 |
|   2441 |
|   3020 |
|   3025 |
|   3174 |
|   3273 |
|   3847 |
|   3862 |
|   3927 |
|   4118 |
|   4175 |
|   4447 |
|   4496 |
|   4545 |
|   4550 |
|   5625 |
|   5643 |
|   5662 |
|   5784 |
|   6344 |
|   6502 |
|   6713 |
|   6714 |
|   6792 |
|   7298 |
|   7339 |
|   7340 |
|   7447 |
|   7476 |
|   7481 |
|   7788 |
|   7853 |
|   8564 |
|   9153 |
|   9190 |
|   9230 |
|   9240 |
|   9261 |
|   9658 |
|   9670 |
|   9736 |
|   9770 |
|  10350 |
|  10369 |
|  10371 |
|  10937 |
|  10962 |
|  11125 |
|  11127 |
|  11515 |
|  11582 |
|  11620 |
|  11626 |
|  12124 |
|  12127 |
|  12871 |
|  12933 |
|  12937 |
|  13072 |
|  13076 |
|  14061 |
|  14074 |
|  14371 |
|  15600 |
|  15689 |
|  16162 |
|  16197 |
|  16204 |
|  16216 |
|  16412 |
|  16421 |
|  16435 |
|  17203 |
|  18151 |
|  18190 |
|  19045 |
|  19252 |
|  19285 |
|  19344 |
|  19385 |
|  19704 |
|  20416 |
|  20422 |
|  20595 |
|  20677 |
|  20680 |
|  20733 |
|  20741 |
|  21160 |
|  21167 |
|  21634 |
|  22647 |
|  22838 |
|  23726 |
|  24791 |
|  24884 |
|  25583 |
|  25616 |
|  25640 |
|  25837 |
|  26200 |
|  26433 |
|  26713 |
|  26721 |
|  27197 |
|  27201 |
|  27983 |
|  29081 |
|  30888 |
|  31828 |
|  32912 |
|  33789 |
|  34485 |
|  35102 |
|  37778 |
|  37783 |
|  38273 |
|  39044 |
|  39045 |
|  39995 |
|  41400 |
|  41974 |
|  42523 |
|  43591 |
|  44050 |
|  44102 |
|  44162 |
|  44168 |
|  44194 |
|  44591 |
|  44881 |
|  44889 |
|  44903 |
|  45178 |
|  45507 |
|  45701 |
|  46209 |
|  46223 |
|  46913 |
|  47367 |
|  47391 |
|  47591 |
|  47601 |
|  48465 |
|  48571 |
|  48579 |
|  48675 |
|  48715 |
|  48993 |
|  49152 |
|  49856 |
|  49979 |
|  50278 |
|  50282 |
|  50294 |
|  50427 |
|  51349 |
|  52071 |
|  52391 |
|  53057 |
|  54218 |
|  54308 |
|  54329 |
|  55379 |
|  55483 |
|  57234 |
|  57499 |
|  58501 |
|  58591 |
|  59524 |
|  59533 |
|  59540 |
|  59666 |
|  61600 |
|  61623 |
|  62771 |
|  62796 |
|  63144 |
|  63890 |
|  63918 |
|  63923 |
|  64170 |
|  64205 |
|  64353 |
|  64424 |
|  64445 |
|  64446 |
|  65054 |
|  65407 |
|  65412 |
|  65420 |
|  65631 |
|  65902 |
|  65946 |
|  65981 |
|  66013 |
|  66066 |
|  67343 |
|  67879 |
|  71093 |
|  71140 |
|  71473 |
|  71601 |
|  72940 |
|  73036 |
|  74764 |
|  74841 |
|  74863 |
|  74980 |
|  75075 |
|  75764 |
|  75768 |
|  75793 |
|  75994 |
|  76394 |
|  76432 |
|  76435 |
|  76481 |
|  77174 |
|  77285 |
|  77328 |
|  77556 |
|  77628 |
|  78134 |
|  78242 |
|  78329 |
|  78366 |
|  79116 |
|  79127 |
|  79136 |
|  79181 |
|  79426 |
|  80124 |
|  80192 |
|  80204 |
|  80229 |
|  80233 |
|  80250 |
|  80288 |
|  80920 |
|  81265 |
|  81367 |
|  81815 |
|  81819 |
|  81870 |
|  82278 |
|  82727 |
|  82777 |
|  82779 |
|  82784 |
|  82802 |
|  82808 |
|  82816 |
|  82839 |
|  82852 |
|  82864 |
|  82867 |
|  82878 |
|  82883 |
|  83565 |
|  83617 |
|  83619 |
|  83628 |
|  84305 |
|  85155 |
|  85176 |
|  85237 |
|  85497 |
|  85773 |
|  85839 |
|  86170 |
|  86642 |
|  86812 |
|  86843 |
|  87823 |
|  88033 |
|  88299 |
|  88814 |
|  89154 |
|  89178 |
|  89801 |
|  89863 |
|  90110 |
|  90509 |
|  90934 |
|  91384 |
|  92153 |
|  92453 |
|  92473 |
|  92475 |
|  92485 |
|  92492 |
|  92766 |
|  92790 |
|  93148 |
|  93289 |
|  93295 |
|  93466 |
|  93914 |
|  94306 |
|  94739 |
|  94801 |
|  94817 |
|  94837 |
|  94950 |
|  95790 |
|  95915 |
|  95919 |
|  95929 |
|  95930 |
|  95936 |
|  95939 |
|  95951 |
|  95953 |
|  95981 |
|  95991 |
|  96038 |
|  96178 |
|  96370 |
|  96378 |
|  96393 |
|  96419 |
|  96449 |
|  96600 |
|  96607 |
|  97257 |
|  98095 |
|  99047 |
|  99850 |
| 100035 |
| 100055 |
| 100068 |
| 100072 |
| 100074 |
| 100087 |
| 100492 |
| 100514 |
| 100544 |
| 100745 |
| 100922 |
| 100929 |
| 101000 |
| 101379 |
| 101922 |
| 101935 |
| 101940 |
| 101949 |
| 101961 |
| 101969 |
| 102190 |
| 104568 |
| 104748 |
| 104759 |
| 104788 |
| 105084 |
| 107285 |
| 108577 |
| 108782 |
| 109101 |
| 109140 |
| 109256 |
| 109560 |
| 109631 |
| 110488 |
| 110994 |
| 111102 |
| 111153 |
| 111341 |
| 111879 |
| 112259 |
| 112360 |
| 112361 |
| 112459 |
| 112499 |
| 112568 |
| 112619 |
| 112624 |
| 112627 |
| 112630 |
| 112650 |
| 112656 |
| 112711 |
| 112729 |
| 112736 |
| 112745 |
| 112754 |
| 112798 |
| 112854 |
| 112855 |
| 112896 |
| 112897 |
| 112900 |
| 112930 |
| 112932 |
| 112937 |
| 112951 |
| 112971 |
| 113038 |
| 113116 |
| 113147 |
| 113154 |
| 113213 |
| 113223 |
| 113243 |
| 113267 |
| 113298 |
| 113309 |
| 113316 |
| 113320 |
| 113326 |
| 113336 |
| 113341 |
| 113347 |
| 113381 |
| 113385 |
| 113396 |
| 113416 |
| 113424 |
| 113426 |
| 113434 |
| 113602 |
| 113616 |
| 113632 |
| 113644 |
| 113693 |
| 113700 |
| 113723 |
| 113792 |
| 113795 |
| 113816 |
| 113882 |
| 113901 |
| 113976 |
| 113979 |
| 114000 |
| 114007 |
| 114041 |
| 114052 |
| 114068 |
| 114125 |
| 114401 |
| 114408 |
| 114417 |
| 114704 |
| 114718 |
| 114747 |
| 115049 |
| 115054 |
|  73880 |
|  79345 |
|  79876 |
|  89836 |
|  95832 |
|  95871 |
|  95872 |
|  96497 |
|  98354 |
| 100357 |
| 100388 |
| 100390 |
| 103711 |
| 106146 |
| 107832 |
| 110011 |
|  87882 |
| 102904 |
| 102910 |
| 111672 |
| 111754 |
+--------+
493 rows in set (3.68 sec)