[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

MySql performance -- joining to a view

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!

Daniel Wilson
Daniel Wilson
1 Solution

I don't believe mySQL supports anything like Oracle's 'Materialized Views.'  It's been talked about but I don't believe it ever happened.  mySQL views can however use the indexes on the underlying tables.  Sometimes, you may have to include a column in the view that is indexed (to join on) that you wouldn't normally need in the view.  I also believe you can use index hints in your query.


Daniel WilsonAuthor Commented:
Thanks.  That confirmed what I suspected.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now