title: How do I get MySQL to use an INDEX for view query?
body: <p>I'm working on a web project with MySql database on Java EE. We needed a view to summarize data from 3 tables with over 3M rows overall. Each table was created with index. But I haven't found out a way to take advantages in the indexes in the conditional select statement retrieval from the view that we created with [group by].</p>
  
  <p>I've getting suggestions from people that <strong>using views in MySql is not a good idea</strong>. Because you can't create index for views in mysql like in oracle. But in some test that I took, indexes can be used in view select statement. Maybe I've created those views in a wrong way.</p>
  
  <p>I'll use a example to describe my problem.</p>
  
  <p>We have a table that records data for high scores in NBA games, with index on column [happend_in]</p>
  
  <pre><code>CREATE  TABLE `highscores` (
     `tbl_id` int(11) NOT NULL auto_increment,
     `happened_in` int(4) default NULL,
     `player` int(3) default NULL,
     `score` int(3) default NULL,
     PRIMARY KEY  (`tbl_id`),
     KEY `index_happened_in` (`happened_in`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>insert data(8 rows)</p>
  
  <pre><code>INSERT INTO highscores(happened_in, player, score)
  VALUES (2006, 24, 61),(2006, 24, 44),(2006, 24, 81),
  (1998, 23, 51),(1997, 23, 46),(2006, 3, 55),(2007, 24, 34), (2008, 24, 37);
  </code></pre>
  
  <p>then I create a view to see the highest score that Kobe Bryant got in each year</p>
  
  <pre><code>CREATE OR REPLACE VIEW v_kobe_highScores
  AS
     SELECT player, max(score) AS highest_score, happened_in
     FROM highscores
     WHERE player = 24
     GROUP BY happened_in;
  </code></pre>
  
  <p>I wrote a conditional statement to see the highest score that <strong>kobe</strong> got in <strong>2006</strong>; </p>
  
  <pre><code>select * from v_kobe_highscores where happened_in = 2006;
  </code></pre>
  
  <p>When I explain it in toad for mysql, I found out that mysql have scan <strong>all rows</strong> to form the view, then find data with condition in it, without using index on [happened_in]. </p>
  
  <pre><code>explain select * from v_kobe_highscores where happened_in = 2006;
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/9fk7A.png" alt="explain result"></p>
  
  <p>The view that we use in our project is built among tables with millions of rows. Scanning all the rows from table in every view data retrieval is unacceptable. Please help! Thanks!</p>
  
  <p>@zerkms Here is the result I tested on real-life. I don't see much differences between. I think @spencer7593 has the right point. <strong>The MySQL optimizer doesn't "push" that predicate down in the view query.</strong>
  <img src="https://i.stack.imgur.com/hNJvu.png" alt="real-life test"></p>