title: MySQL view taking too much time to select data
body: <p>In the web page that I'm working on I need to show some statistics based on a different user details which are in three tables. So I have the following query that I join to more different tables:</p>
  
  <pre><code>SELECT *
  FROM `user` `u`
  LEFT JOIN `subscriptions` `s` ON `u`.`user_id` = `s`.`user_id`
  LEFT JOIN `devices` `ud` ON `u`.`user_id` = `ud`.`user_id`
  GROUP BY `u`.`user_id`
  </code></pre>
  
  <p>When I execute the query with <code>LIMIT 1000</code> it takes about <strong>0.05</strong> seconds and since I'm using the data from all the three tables in a lot of queries I've decided to put it inside a <code>VIEW</code>:</p>
  
  <pre><code>CREATE VIEW `user_details` AS ( the same query from above )
  </code></pre>
  
  <p>And now when I run:</p>
  
  <pre><code>SELECT * FROM user_details LIMIT 1000
  </code></pre>
  
  <p>it takes about <strong>7-10</strong> seconds.</p>
  
  <p>So my question is can I do something to optimize the view because the query seems to be pretty quick or I should the whole query instead of the view ?</p>
  
  <p>Edit: this is what <code>EXPLAIN SELECT * FROM user_details</code> returns </p>
  
  <pre><code>+----+-------------+------------+--------+----------------+----------------+---------+------------------------+--------+-------+
  | id | select_type | table      | type   | possible_keys  | key            | key_len | ref                    | rows   | Extra |
  +----+-------------+------------+--------+----------------+----------------+---------+------------------------+--------+-------+
  |  1 | PRIMARY     | &lt;derived2&gt; | ALL    | NULL           | NULL           | NULL    | NULL                   | 322666 |       |
  |  2 | DERIVED     | u          | index  | NULL           | PRIMARY        | 4       | NULL                   | 372587 |       |
  |  2 | DERIVED     | s          | eq_ref | PRIMARY        | PRIMARY        | 4       | db_users.u.user_id     |      1 |       |
  |  2 | DERIVED     | ud         | ref    | device_id_name | device_id_name | 4       | db_users.u.user_id     |      1 |       |
  +----+-------------+------------+--------+----------------+----------------+---------+------------------------+--------+-------+
  </code></pre>
  
  <p><strong>4 rows in set (8.67 sec)</strong></p>
  
  <p>this is what <code>explain</code> retuns for the query:</p>
  
  <pre><code>+----+-------------+-------+--------+----------------+----------------+---------+------------------------+--------+-------+
  | id | select_type | table | type   | possible_keys  | key            | key_len | ref                    | rows   | Extra |
  +----+-------------+-------+--------+----------------+----------------+---------+------------------------+--------+-------+
  |  1 | SIMPLE      | u     | index  | NULL           | PRIMARY        | 4       | NULL                   | 372587 |       |
  |  1 | SIMPLE      | s     | eq_ref | PRIMARY        | PRIMARY        | 4       | db_users.u.user_id     |      1 |       |
  |  1 | SIMPLE      | ud    | ref    | device_id_name | device_id_name | 4       | db_users.u.user_id     |      1 |       |
  +----+-------------+-------+--------+----------------+----------------+---------+------------------------+--------+-------+
  </code></pre>
  
  <p><strong>3 rows in set (0.00 sec)</strong></p>