I am running MySql 5.
I have a table that does effectivity dating to track users. When a user goes inactive, has his performance goal changed, has his phone extension changed, etc., a new record is added.
I have a view on this table that presents the current data -- current active state, phone extension, performance goal, etc.
Currently the table has 346 records and the view 53.
I have a complex query that joins to the view. But I can't add indexes to a view:
An EXPLAIN on the complex query suggests table scans of the table itself.
How should I optimize this? Do I need to optimize the view itself by adding indexes to the table? Surely I should not replace the view references in my complex query by pasting in the view definition as a derived table!