EXPLAIN query

ActivePublic

Actions

MariaDB LABS localhost enwiki_p > explain 
    -> select user_id, user_name, reg_time, count(log_id) as moves 
    -> from 
    -> (
    ->   select user_id, 
    ->          user_name, 
    ->          user_registration, 
    ->          now()-str_to_date(user_registration, '%Y%m%d%H%i%s') as reg_time 
    ->   from user 
    ->   order by user_id desc 
    ->   limit 10
    -> ) as user_list, 
    -> logging 
    -> where user_id = log_user and log_type='move'\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: logging
         type: ref
possible_keys: type_time
          key: type_time
      key_len: 34
          ref: const
         rows: 6583402
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: user
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
        Extra: 
3 rows in set (0.00 sec)

MariaDB LABS localhost enwiki > explain 
    -> select user_id, user_name, reg_time, count(log_id) as moves 
    -> from 
    -> (
    ->   select user_id, 
    ->          user_name, 
    ->          user_registration, 
    ->          now()-str_to_date(user_registration, '%Y%m%d%H%i%s') as reg_time 
    ->   from user 
    ->   order by user_id desc 
    ->   limit 10
    -> ) as user_list, 
    -> logging 
    -> where user_id = log_user and log_type='move'\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: logging
         type: ref
possible_keys: type_time,user_time
          key: user_time
      key_len: 4
          ref: user_list.user_id
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: user
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
        Extra: 
3 rows in set (0.00 sec)


MariaDB LABS localhost enwiki_p > explain 
    -> select user_id, user_name, reg_time, count(log_id) as moves 
    -> from 
    -> (
    ->   select user_id, 
    ->          user_name, 
    ->          user_registration, 
    ->          now()-str_to_date(user_registration, '%Y%m%d%H%i%s') as reg_time 
    ->   from user 
    ->   order by user_id desc 
    ->   limit 10
    -> ) as user_list, 
    -> logging_userindex
    -> where user_id = log_user and log_type='move'\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: logging
         type: ref
possible_keys: type_time,user_time
          key: user_time
      key_len: 4
          ref: user_list.user_id
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: user
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
        Extra: 
3 rows in set (0.01 sec)

select
        user_id,
        user_name,
        now() - str_to_date(user_registration, '%Y%m%d%H%i%s') as reg_time,
        count(log_id) as moves
from
        user,
        logging_userindex
where
        user_id = log_user
    and log_type = 'move'
group by user_id
order by user_id desc
limit 10;