Mysql – View created using Join Queries taking too much to load

join;loadMySQLview

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.

Best Answer

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

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

Related Solutions

Sql-server – How should row-specific metadata be created handled for an outer join view

If you're just trying to do a batched update to attach a bunch of tickets to an invoice (i.e. Invoices and tickets live in a 1:M relationship) then you have to get a list of ticket IDs into the stored procedure in the first place.

You could use a table variable as an input parameter if you have a version of SQL Server that supports these. Otherwise, you would have to encode the list of ticket IDs in some way, for example a list serialised as a string. You could encode the list as XML if you don't mind eternal damnation :)

If you're trying to add an Invoice record to a batch of tickets (i.e tickets live in a 1:0-1 relationship with invoices) then you have the same options for input.

In both cases the most efficient way to do the updates is likely to be creating a table variable with your list of invoice changes and then doing an update/insert operation joining the table variable against the main tables.

OTOH I can't think of anything more elegant than that.

If your problem is passing the list in across from the client via then you can pass in table-valued parameters in recent versions of ADO.NET. In older versions you are basically in the job of scrubbing and escaping the identifiers client side and passing the list across serialised as a string or XML (supported from .NET 2.0 and SQL Server 2005 onwards).

If you're using anything else (e.g. PHP) then you're probably going to have to drop down to the lowest common denominator - scrub the list of tickets and encode them as a string. Maybe the stored procedure can do some validation, such as checking the tickets all belong to the correct user.

Mysql – Query taking too much time

The core issue appears to be that the optimizer does not (or cannot) use the index idx_17109 to seek for the c.precedence IS NOT NULL predicate. The following modification allows the seek, but still requires a hint to avoid the sort:

SELECT
    pcm.catalog_id 
FROM cat_catal_defer AS c USE INDEX (idx_17109)
JOIN cat_produ_catal_map_defer AS pcm ON
    pcm.catalog_id = c.catalog_id
JOIN cat_catal_catal_map_defer AS ccm ON
    ccm.catalog_id = pcm.catalog_id
WHERE
    c.precedence > ''
    AND pcm.product_id = 2520000
    AND ccm.parent_catalog_id = 1000025
    AND ccm.levels <> 0
ORDER BY
    c.precedence;

Explain:

Explain table

Try the SQLFiddle here.

Best Answer

Related Solutions

Sql-server – How should row-specific metadata be created handled for an outer join view

Mysql – Query taking too much time

Related Question