EXPLAIN query
ActivePublic
Actions

Authored by GoldenRing on Aug 3 2015, 7:42 AM.

Tags

Referenced Files

	F326728: EXPLAIN query
	Aug 3 2015, 7:43 AM

	F326715: EXPLAIN query
	Aug 3 2015, 7:42 AM

Subscribers

coren

	Could someone please EXPLAIN this query for me:


	select
	user_id,
	user_name,
	timestampdiff (HOUR, str_to_date(user_registration, '%Y%m%d%H%i%s'), now())/24 as registered_time,
	count(log_id) as moves
	from
	user,
	logging_userindex
	where
	user_id = log_user
	and (log_action = 'move' or log_action = 'move_redir')
	group by user_id
	order by user_id desc
	limit 1000;

Event Timeline

GoldenRing edited the content of this paste. (Show Details)Aug 3 2015, 7:42 AM

GoldenRing changed the title of this paste from untitled to EXPLAIN query.

GoldenRing updated the paste's language from autodetect to mysql.

GoldenRing added a project: Tool-Database-Queries.

GoldenRing edited the content of this paste. (Show Details)

On enwiki:

+------+-------------+---------+-------+---------------------+-----------+---------+---------------------+----------+-------------+
| id   | select_type | table   | type  | possible_keys       | key       | key_len | ref                 | rows     | Extra       |
+------+-------------+---------+-------+---------------------+-----------+---------+---------------------+----------+-------------+
|    1 | SIMPLE      | user    | index | PRIMARY             | PRIMARY   | 4       | NULL                | 21705642 |             |
|    1 | SIMPLE      | logging | ref   | type_time,user_time | user_time | 4       | enwiki.user.user_id |        1 | Using where |
+------+-------------+---------+-------+---------------------+-----------+---------+---------------------+----------+-------------+

Okay then, so I'm guessing it's the GROUP BY / ORDER BY that are taking all the time, right?

That is most likely the case - the grouping and ordering requires the entire result set before it can return any rows.

EXPLAIN queryActivePublicActions

Event Timeline

EXPLAIN query
ActivePublic
Actions