MySQL Forums
Forum List  »  MyISAM

slow query / table lock
Posted by: Thomas Manninger
Date: June 06, 2016 01:55AM

Hello,

i have some tables with more than 10million entries.

Once per week, i create some statistics, the statistic sql takes about 1h (query is creating a temporary table):

SELECT AddressID, COUNT(1) AS Num FROM (
SELECT M.AddressID FROM Statistik AS M
JOIN StatistikCTR AS C
ON M.AddressID = C.AddressID
AND M.ProjID = C.ProjID
WHERE M.ProjID IN (...many ids...)
AND M.AddressDate >= '2015-12-01'
GROUP BY M.AddressID, M.ProjID
) AS S
GROUP BY AddressID

The problem is, that the table is locked for writing while the query is running.

What is the best way to solve the problem?
- Run setup a mysql slave on run the slow queries on the slave server?
- Split the query into many single queries?

Thanks for help!

Options: ReplyQuote


Subject
Views
Written By
Posted
slow query / table lock
18526
June 06, 2016 01:55AM
1369
June 06, 2016 02:06AM
1632
June 13, 2016 01:55AM
1266
June 13, 2016 01:57AM


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.