MySql performance -- joining to a view

Posted on 2011-10-04
Last Modified: 2012-05-12
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!

Question by:Daniel Wilson
    LVL 13

    Accepted 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.


    LVL 32

    Author Closing Comment

    by:Daniel Wilson
    Thanks.  That confirmed what I suspected.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi ( had suggested a “sed” way, I actually shell …
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now