CODEWITHSUNDEEP
mysqldatabasedatabase-designinnodb
carlos
carlos

Reputation: 317

How big is too big for a view in MySQL InnoDB?

BACKGROUND

I'm working with a MySQL InnoDB database with 60+ tables and I'm creating different views in order to make dynamic queries fast and easier in the code. I have a couple of views with INNER JOINS (without many-to-many relationships) of 20 to 28 tables SELECTING 100 to 120 columns with row count below 5,000 and it works lighting fast.

ACTUAL PROBLEM

I'm creating a master view with INNER JOINS (without many-to-many relationships) of 34 tables and SELECTING about 150 columns with row count below 5,000 and it seems like it's too much. It takes forever to do a single SELECT. I'm wondering if I hit some kind of view-size limit and if there is any way of increasing it, or any tricks that would help me pass through this apparent limit.

It's important to note that I'm NOT USING Aggregate functions because I know about their negative impact on performance, which, by the way I'm very concerned about.

Upvotes: 5

Views: 3338

Answers (2)

davek
davek

Reputation: 22925

MySql does not use the "System R algorithm" (used by Postgresql, Oracle, and SQL Server, I think), which considers not only different merge algorithms (MySQL only has nested-loop, although you can fake a hash join by using a hash index), but also the possible ways of joining the tables and possible index combinations. The result seems to be that parsing of queries - and query execution - can be very quick upto a point, but performance can dramatically drop off as the optimizer chooses the wrong path through the data.

Take a look at your explain plans and try to see if a) the drop in performance is due to the number of columns you are returning (just do SELECT 1 or something) or b) if it is due to the optimizer choosing a table scan instead of index usage.

Upvotes: 5

Mathias Bak
Mathias Bak

Reputation: 5135

A view is just a named query. When you refer to a view in MySQL it just replaces the name with the actual query and run it.

It seems that you confuse it with materialized views, which are tables you create from a query. Afterwards you can query that table, and does not have to do the original query again.

Materialized views are not implemented in MySQL.

To improve the performance try to use the keyword explain to see where you can optimize your query/view.

Upvotes: 3

Related Questions