CODEWITHSUNDEEP
mysqlperformanceselectviewlatency
S R
S R

Reputation: 167

MYSQL View vs Select Performance and Latency

Every bit of literature I've found on this topic suggests that the advantage of using views lies in how much cleaner it makes the code. What I'd like to know is if I fire multiple selects one after the other, will the latency (Apache to MySQL to Apache multiple times) be significantly higher than using a complicated view. I have a scenario where I can take one of two approaches:

  1. Use individual queries. As I understand it, this is what MySQL does anyway even when I use views. But will there be a latency issue since I will be executing one query, parsing it on the PHP backend to get the filters for the next query and then sending that query over to the MySQL server (and so on, say, anywhere between 1 to 4 times, i.e., I will be running 2 to 5 queries in total)? In other words, Query1->PHP->Query2->PHP->...
  2. Build complex views. Since it's likely I will have elements in one table with no corresponding entry in the other, the final view is likely to have multiple UNIONS or JOINS even if it is being created out of two tables. But since there is no question of latency here and all the processing and filtering is done at the MySQL server side, will there be an improvement in performance?

What would help me deliver pages faster to my users?

Any help based on actual observation or proven documentation will be welcome.

Thanks.

SR

Upvotes: 2

Views: 6950

Answers (2)

nacho
nacho

Reputation: 5397

Views don't modify the performance of your database. The performance is the same as the select it includes. The views are not used to increment performance or deliver pages faster, they are used for two things:

  • The use of a view lets you grant permisions to users on different tables so they don't see all information in the table (they only see what you want and in the way you want)
  • If you use the same select query in different places in your code, if you need to modify it, you can just modify the view and not every query in your code

Upvotes: 10

Rick James
Rick James

Reputation: 142208

It depends.

On the one hand, 90% of a simple query is in the overhead (communication, parsing, etc). So, fewer roundtrips to the server is better.

On the other hand, a query (with or without VIEWs) that is too complex may do things less efficiently than you could do by breaking it down into simple steps.

I say VIEWs are syntactic sugar, not a performance benefit. (@nacho says it better.)

I have built many complex pages, with, say, 40 queries in it. Performance is decent. I locate any "slow" queries (the SlowLog is handy for this), then optimize them.

I also find it better to build a page with extra info on it, rather than forcing the user to click on something to bring up another page. That is, doubling the number of SELECTs (costing of a few milliseconds) can lead to a better UI.

Upvotes: 3

Related Questions