1

I have written a query to create a view which i can use as a flat table in my application, which is having more than 30 inner join conditions across 25 tables. Now the problem is every time i am selecting the view with even 2-3 columns without adding any further condition or clause, it is taking more that 45-50 seconds to load.

No of records in the view : around 250

No of Columns in view : 45-50

My.cnf seems okay, i have checked it using mysqltuner perl script. I have tweaked the relevant parameters in my.cnf , but no significant effect noticed in query response time.

Engine Type : InnoDB, Foreign key constraints applied.

Mysql version : Percona mysql 5.6

Any Ideas?

Thanks in Advance.

  • well views of joins sometime does behave the same in MySQL..have you tried changing the view algorithm from default to either Merge or temptable to see if it give some gain?. – Nawaz Sohail Jun 13 '16 at 13:52
  • Do the tables have indexes? – ypercubeᵀᴹ Jun 13 '16 at 18:48
  • @ypercube, tables are indexed properly. – Sachin Singh Jun 14 '16 at 9:29
  • Show us the execution plan then. If the tables are properly indexed and the view has only 250 rows, I don't see how a decent server would need 45 seconds. It should be much faster. – ypercubeᵀᴹ Jun 14 '16 at 9:34
  • Does EXPLAIN SELECT ... take nearly 45 seconds also? – Rick James Jun 27 '16 at 4:57
1

Your view will join all the tables involved in the view definition in order to create the required view.

First, of course, you should check that your tables are properly indexed so that the joins are as efficient as possible when creating the view.

However, joining 25 tables with 30 inner joins is a relatively expensive undertaking, resulting in your case with the 45-50 seconds of delay before getting results.

A good general strategy is to break a big slow step into smaller faster steps. I would consider whether it is possible to create 4 or 5 temporary tables into which you can insert smaller data sets. Then once the temporary tables exist you could join them together to get a final result.

This does require some design effort in creating the temporary tables so that the individual tables create the proper subsets of data for each query.

You can create a Materialized View which may work for you depending on your requirements. Rather than explain the details please look at:

http://www.fromdual.com/mysql-materialized-views

  1. You must write code to populate the materialized view and to refresh the data as needed.
  2. If you must have the most current data, then you will need to write triggers to provide the needed updates.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.