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?