CODEWITHSUNDEEP
mysqlsqlperformance
user2062455
user2062455

Reputation: 421

msql query or mysql view

This is a performance question.

Lets say I have the following mysql data (over 1,000 records of the same structure)

table name: hours

Employee | Hours | Scope    |  Project
 John        4      labour        projId_A
 John        3      Travel        projId_A
 Mark        4      labour        projId_A
 Mark        2      Travel        projId_A

Is it better performance if I do one query with the summaries of labour and travel to get this

table name: projects (i would left join projects and hours)

Project  |  labourHrs  |  travelHrs
projId_A      8             5

or us is it better to

  1. create a view that shows only labourHrs
  2. create a view that shows only travelHrs

Then create 2 joints projects -> labourH and projects -> travelH

to get:

Project  |  labourHrs  |  travelHrs
projId_A      8             5

i guess i am asking, is it better performance to do the sum inside the project query or inside the labour_view & travel_view'

any thoughts?

Upvotes: 0

Views: 46

Answers (2)

tale852150
tale852150

Reputation: 1628

Views are generally used to hide data or simplify a query. Use your first option and do the SUM in the initial SQL.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

It is better to do a single query:

select project
       sum(case when score = 'labour' then hours else 0 end) as labourHrs,
       sum(case when score = 'travel' then hours else 0 end) as travelhours
from t
group by project;

From a performance perspective in MySQL, views do not improve performance. They might not affect performance, but they won't make it better.

Upvotes: 3

Related Questions