USE INDEX in VIEWS

Question

I have a query like this:

SELECT * 
  FROM view_name 
  WHERE column1 = something AND column2 = somethingelse

The underlying table has indexes on both column1 and column2, but MySQL is using the wrong index. How do I force it to use the right index. Something like this:

SELECT * 
  FROM view_name USE INDEX (table_name.column2) 
  WHERE column1 = something AND column2 = somethingelse

Edit: Someone asked for this:

SHOW CREATE VIEW view1
CREATE ALGORITHM=UNDEFINED DEFINER=`x`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `table1`.`id` AS `identifier`,`table1`.`col1` AS `column1`,`table1`.`col2` AS `column2` from `table1`

SHOW CREATE TABLE table1
CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(100) NOT NULL,
  `col2` varchar(100) NOT NULL,
  `col3` varchar(100) NOT NULL,
  `col4` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `col1` (`col1`),
  KEY `col2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Edit: More detailed explanation:

If I do this:

SELECT * FROM table1 WHERE col1 = x AND col2 = y

MySQL chooses the index on col1 and I get poor performance.

If I do this:

SELECT * FROM table1 USE INDEX (col2) WHERE col1 = x AND col2 = y

MySQL uses the index on col2 and I get great performance.

How do I do the same thing, but on the view? Something like this:

SELECT * FROM view1 USE INDEX (column2) WHERE column1 = x AND column2 = y

How do I tell MySQL what index to use when running the query on the view?

Edit: I just found this:

Views do not have indexes, so index hints do not apply. Use of index hints when selecting from a view is not permitted.

https://dev.mysql.com/doc/refman/5.7/en/view-restrictions.html

So, maybe what I want is not possible.

possible dupe: https://stackoverflow.com/questions/13944946/how-do-i-get-mysql-to-use-an-index-for-view-query — sniperd, Jun 11 '18 at 17:37
Can you show `SHOW CREATE VIEW view` and the `SHOW CREATE TABLE table` statements for every table involved in the view — Raymond Nijland, Jun 11 '18 at 17:41
Try recreating the VIEW with ALGORITHM=MERGE then the MySQL optimizer can optimize selects on views better https://dev.mysql.com/doc/refman/8.0/en/view-algorithms.html — Raymond Nijland, Jun 11 '18 at 18:38
Please add the output of `explain format=json select ... from table1 ...` to your question, for both index variants (so without `use` and with `use`). Also make sure that x and y are actual strings (have `'` around them), not numbers (e.g `and column2 = 1`) — Solarflare, Jun 12 '18 at 07:19

score 1 · Answer 1 · answered Jun 11 '18 at 17:33

1

The underlying table has indexes on both column1 and column2...

What do you mean by that? For this query to be fast you don't need just two SEPARATE indexes on each column, but a combined index. Try adding the followinfg index:

create index ix1 on table_name (column1, column2);

Without this one MySQL can try using any of the less optimal indexes.

answered Jun 11 '18 at 17:33

The Impaler

45,731
9
39
76

You assume the columns are existing in one table.. A view can select from multiple tables. – Raymond Nijland Jun 11 '18 at 17:43
Yes, he talks about "the underlying table". – The Impaler Jun 11 '18 at 17:46
Two indexes are needed because sometimes the query involves only one of the columns. – user34814 Jun 11 '18 at 18:13
That's OK. However, two separated indexes do not help a combined query such as this one. Well... technically they can help, but not a great deal usually. You need this third index for the combined search criteria. – The Impaler Jun 11 '18 at 18:14
I disagree. They help a lot. – user34814 Jun 11 '18 at 18:27
As I said, they can help, but it isually depends on the "selectivity" of the columns. So how selective are they? In other words, how many rows does an equality filter return? 50%, 5%, 0.5%? If it's below 5% (ideally below 0.5%) then yes, a single column index will act as the "access" (very efficient) while the second one will act as the "filter" (less efficient). If the first one is efficient your query will still be fast, at least for the time being. A combined query, on the other hand, will make both columns act as "access" -- something that is very efficient. – The Impaler Jun 11 '18 at 18:39
For this query, col2 = x is very selective (i.e. return fewer rows), while col1 = y is not (i.e. returns much more rows). But MySQL chooses to use the index on col1. – user34814 Jun 11 '18 at 18:57
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172911/discussion-between-user34814-and-the-impaler). – user34814 Jun 11 '18 at 19:03

USE INDEX in VIEWS

1 Answers1