jcrespo added a subscriber: jcrespo.
jcrespo added a comment.
Yes, for this particular case (machine, table and parameters), not using a
FORCE INDEX will be better:
With FORCE:
mysql> EXPLAIN SELECT
rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,rc_comment,rc_user,rc_user_text,rc_minor,rc_type,rc_bot,rc_old_len,rc_new_len,rc_logid,rc_log_type,rc_log_action,rc_params,page_is_redirect
FROM `recentchanges` FORCE INDEX (rc_timestamp) LEFT JOIN `page` ON
((rc_namespace=page_namespace) AND (rc_title=page_title)) WHERE rc_namespace
IN ('6','7') AND rc_type IN ('0','1','3') ORDER BY rc_timestamp DESC,rc_id
DESC LIMIT 6\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: recentchanges
type: index
possible_keys: NULL
key: rc_timestamp
key_len: 16
ref: NULL
rows: 6
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: page
type: eq_ref
possible_keys: name_title
key: name_title
key_len: 261
ref:
wikidatawiki.recentchanges.rc_namespace,wikidatawiki.recentchanges.rc_title
rows: 1
Extra:
2 rows in set (0.02 sec)
Execution time:
Empty set (20.50 sec)
PAGER set to stdout
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 0 |
| Handler_icp_attempts | 0 |
| Handler_icp_match | 0 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 6847234 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+---------+
25 rows in set (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 2;
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000133 |
| checking permissions | 0.000009 |
| checking permissions | 0.000007 |
| Opening tables | 0.000023 |
| After opening tables | 0.000010 |
| System lock | 0.000008 |
| Table lock | 0.000066 |
| After table lock | 0.000009 |
| init | 0.000050 |
| optimizing | 0.000021 |
| statistics | 0.000034 |
| preparing | 0.000034 |
| executing | 0.000007 |
| Sorting result | 0.000010 |
| Sending data | 22.417293 |
| end | 0.000028 |
| query end | 0.000013 |
| closing tables | 0.000029 |
| freeing items | 0.000017 |
| updating status | 0.000033 |
| logging slow query | 0.000014 |
| cleaning up | 0.000012 |
+----------------------+-----------+
22 rows in set (0.00 sec)
Without force:
mysql> EXPLAIN SELECT
rc_id,rc_timestamp,rc_namespace,rc_title,rc_cur_id,rc_type,rc_deleted,rc_this_oldid,rc_last_oldid,rc_comment,rc_user,rc_user_text,rc_minor,rc_type,rc_bot,rc_old_len,rc_new_len,rc_logid,rc_log_type,rc_log_action,rc_params,page_is_redirect
FROM `recentchanges` LEFT JOIN `page` ON ((rc_namespace=page_namespace) AND
(rc_title=page_title)) WHERE rc_namespace IN ('6','7') AND rc_type IN
('0','1','3') ORDER BY rc_timestamp DESC,rc_id DESC LIMIT 6\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: recentchanges
type: range
possible_keys: rc_namespace_title,rc_ns_usertext,tmp_3
key: rc_namespace_title
key_len: 4
ref: NULL
rows: 2
Extra: Using index condition; Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: page
type: eq_ref
possible_keys: name_title
key: name_title
key_len: 261
ref:
wikidatawiki.recentchanges.rc_namespace,wikidatawiki.recentchanges.rc_title
rows: 1
Extra:
2 rows in set (0.15 sec)
Execution time:
Empty set (0.00 sec)
PAGER set to stdout
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 0 |
| Handler_icp_attempts | 0 |
| Handler_icp_match | 0 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
25 rows in set (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 6;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000127 |
| checking permissions | 0.000010 |
| checking permissions | 0.000008 |
| Opening tables | 0.000037 |
| After opening tables | 0.000011 |
| System lock | 0.000009 |
| Table lock | 0.000009 |
| After table lock | 0.000010 |
| init | 0.000059 |
| optimizing | 0.000025 |
| statistics | 0.000126 |
| preparing | 0.000043 |
| executing | 0.000009 |
| Sorting result | 0.000046 |
| Sending data | 0.000040 |
| end | 0.000010 |
| query end | 0.000012 |
| closing tables | 0.000013 |
| freeing items | 0.000012 |
| updating status | 0.000033 |
| cleaning up | 0.000009 |
+----------------------+----------+
21 rows in set (0.00 sec)
However, are you sure it wouldn't affect the same query with other parameters
(edge cases)?
Maybe it is needed in 5.5 but not in 10/5.6. Maybe it is needed when page
sampling is wrong. FORCE index is a (sometimes necessary) evil, due usually to
the lack of documentation of why it was set there.
My point of view would be to prove it statistically- set one of our machines
with a proposed change and use pt-query-digest
<https://wikitech.wikimedia.org/wiki/MariaDB/query_performance> (example output
<https://wikitech.wikimedia.org/wiki/MariaDB/parsercache>) or
performance_schema to prove it is better overall and with no regression in a
controlled environment, over a long period of time.
We are trying to improve the process on our side to better help you with this,
too.
TASK DETAIL
https://phabricator.wikimedia.org/T101502
EMAIL PREFERENCES
https://phabricator.wikimedia.org/settings/panel/emailpreferences/
To: jcrespo
Cc: jcrespo, Anomie, Aklapper, jayvdb, Krenair, Legoktm, greg, pywikipedia-bugs
_______________________________________________
pywikibot-bugs mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/pywikibot-bugs
