We have MyISAM tables on MySQL 5.5 and there is this select * from theView where serial_number = 'QWE12345' or select * from theView where user_id = 1234 kind of queries when searching. When I profile these queries I see that most of the time is spend on "sending data" aka on searching the row/rows or filtering the table. And also I have to say that the theview has few joins and the serial number and user id has their own indexes on the table that they are located.

So I know that using view is bad idea but this is a 8 years old database/application design. And also I have this log for example:

# Query_time: 76.050350  Lock_time: 23.576348 Rows_sent: 2  Rows_examined: 890596
SET timestamp=1418631993;
select * from theView where 1=1  and serial_number = 'TRSNDLD032106700004139'  and LOCATION<>3623 order by  RECNO desc limit 0,25;

It looks like although serial_number has an index in its table, the MySQL does not use this index because now the serial number is searched in the view.

How can I improve performance? Can I force somehow to use index when using views?

  • Can you share the show create view theView and all the show create table XXX for every table involved in the view? – Cristian Porta Dec 16 '14 at 7:59
  • you can provide index hint using following statement. SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3; provide this in view definition and see if it impacts performance. – Nawaz Sohail Dec 16 '14 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.