MySQL Forums
Forum List  »  InnoDB

MySQL 5.5 performance is slow when querying cross database union view
Posted by: Dan Lay
Date: July 10, 2014 03:17AM

Our software populates a new database every year with our clients financial records.
We keep each year in a separate database as a way of archiving old data.
Sometimes there is a requirement to query data from more than one year at once.

To get around this I have created a 3rd database that has a view named `orders` that unions all all records from the `orders` tables in the year 1 and year 2 databases. This gives us all records for year 1 and year 2.
The identity keys are carried forward when a new database is created.
The idea is that we can use the same report query for both single and multiple databases.

I'm getting very slow performance on relatively simple queries.

For example a query that may take 7 seconds to run on a large year database can take 7 minutes when querying via the view. It does this even if there is a very small amount of data in the year 2 database.

I suspect that this behaviour is due to the way I am selecting all records in the union.
Does anyone know of a way to optimise this and improve performance?

A typical table may have an excess of 300000 records and is over 200mb.
I have set innodb_buffer_pool_size to 512M
Any help or suggestions would be much appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL 5.5 performance is slow when querying cross database union view
3040
July 10, 2014 03:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.