Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've a table that contains weekly statistical infos.

ex.

Society - Week - Year - Data
AAA Inc - 1 - 2014 - 10250
AAA Inc - 2 - 2014 - 5235
BBB Inc - 2 - 2014 - 3210
...

Over this table i need to perform some aggregate functions (group by Society and Year and Sum data for example). For the most frequent of this i wanna create a View.

ex.

Society - Year - Data Sum
AAA Inc - 2014 - 15485
BBB Inc - 2014 - 3210

Than over this view i need to not only select data (with filters and ordering) but also to perform other aggregate functions (for example the Max(Data) for the AAA Society).

My questions are: - Can this approac improve performance? How much? - Is possible to add some index to view to improve some operations?

Thanks a lot!

share|improve this question

migrated from stackoverflow.com Feb 12 at 8:37

This question came from our site for professional and enthusiast programmers.

    
I would suspect it will degrade performance unless you are very carefully. MySQL will use one index on a table, and won't carry them up from a sub query (which is what the view will effectively be). –  Kickstart Feb 11 at 11:16
    
So it's okay to use a view like that but not think about index...right? –  user2540463 Feb 11 at 15:56
    
To SQL a view just acts as a table (or a sub query), so yes you can use it like that. But as the indexes might not be useful the performance might be slow. –  Kickstart Feb 11 at 16:16
    
@Kickstart MySQL can use more than one index of a table in one query. –  ypercube Feb 12 at 9:10
    
So is or isn't usefull to add some index for example over DataSum comlumn? Sorry but now i'm a little confused :) –  user2540463 Feb 12 at 11:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.