I am using a
InnoDB database with 60+ tables and I am creating different sights to be able to make dynamic queries fast and simpler within the code. I've got a handful of sights with INNER JOINS (without many-to-many associations) of
20 to 28 tables Choosing
100 to 120 columns with
row count below 5,000 and delay pills work lighting fast.
I am developing a master view with INNER JOINS (without many-to-many associations) of
34 tables and Choosing about
150 columns with
row count below 5,000 also it appears like it's an excessive amount of. It requires forever to perform a single Choose. I am wondering basically hit some type of view-size limit and when there's in whatever way of growing it, or any methods that will assist me to go through this apparent limit.
You need to observe that I am NOT USING
Aggregate functions because I understand regarding their negative effect on performance, which, incidentally I am very worried about.
MySql doesn't make use of the "System R formula" (utilized by Postgresql, Oracle, and SQL Server, I believe), which views not just different merge calculations (MySQL has only nested-loop, even though you can fake a hash join using a hash index), but the good ways of joining the tables and possible index combinations. The end result appears to become that parsing of queries - and query execution - can be quite quick up to a place, but performance can significantly fall off because the optimizer selects the incorrect path with the data.
Have a look at the explain plans and then try to find out if a) the stop by performance is because of the amount of posts you're coming back (simply do Choose 1 or something like that) or b) if it's because of the optimizer selecting a table scan rather than index usage.
A view is simply a named query. Whenever you make reference to a view in MySQL it simply replaces the title using the actual query and run it.
It appears that you simply confuse it with materialized sights, that are tables you create from the query. Later on you are able to query that table, and doesn't need to do the initial query again.
Materialized sights aren't implemented in MySQL.
To enhance the performance use the keyword explain to determine where one can optimize your query/view.