Odradio sam predlozenu proceduru, ali bez vidljivih rezultata:
Kôd:
mysql> select id,domain from odm_result_keywords_es where whois_status is null and (ovt_ext > 0 or ovt_ext_local > 0) limit 100;
Empty set (29.15 sec)
mysql> explain select id,domain from odm_result_keywords_es where whois_status is null and (ovt_ext > 0 or ovt_ext_local > 0) limit 100;
+----+-------------+------------------------+------+---------------------------------------+-----------------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+------+---------------------------------------+-----------------+---------+-------+---------+-------------+
| 1 | SIMPLE | odm_result_keywords_es | ref | ovt_ext,ovt_ext_local,whois_ovt_local | whois_ovt_local | 103 | const | 2088308 | Using where |
+----+-------------+------------------------+------+---------------------------------------+-----------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)
Dakle, i dalje ne valja
30 sekundi traje query, koristi taj novi index, ali ima 2088308 redova sto su skoro svi.
Evo indexa koji sada postoje u tabeli:
Kôd:
mysql> show index from odm_result_keywords_es;
+------------------------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| odm_result_keywords_es | 0 | PRIMARY | 1 | id | A | 2090574 | NULL | NULL | | BTREE | |
| odm_result_keywords_es | 0 | domain | 1 | domain | A | 2090574 | NULL | NULL | | BTREE | |
| odm_result_keywords_es | 1 | ovt_ext | 1 | ovt_ext | A | 138 | NULL | NULL | YES | BTREE | |
| odm_result_keywords_es | 1 | ovt_ext_local | 1 | ovt_ext_local | A | 77 | NULL | NULL | YES | BTREE | |
| odm_result_keywords_es | 1 | whois_ovt_local | 1 | whois_status | A | 2090574 | NULL | NULL | YES | BTREE | |
| odm_result_keywords_es | 1 | whois_ovt_local | 2 | ovt_ext | A | 2090574 | NULL | NULL | YES | BTREE | |
| odm_result_keywords_es | 1 | whois_ovt_local | 3 | ovt_ext_local | A | 2090574 | NULL | NULL | YES | BTREE | |
+------------------------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)