We have a MySQL server that runs the following query:
SELECT MIN(signalid) FROM ((SELECT MIN(signalid) AS signalid FROM tradesignals inner join tradesystems on tradesignals.systemid=[login to view URL] inner join usersystemx on [login to view URL] = [login to view URL] WHERE cmd < 6 and usersystemx.acnt_id = 'eforex' AND signalid NOT IN (SELECT signalid FROM usersignalx WHERE acnt_id = 'eforex') AND signalid NOT IN (SELECT [login to view URL] FROM tradesignals AS earlier JOIN tradesignals AS later ON [login to view URL] = later.assoc_signalid WHERE [login to view URL] = 6 or [login to view URL] = 7)) UNION (SELECT MIN(signalid) AS signalid FROM tradesignals WHERE cmd = 9 AND signalid NOT IN (SELECT signalid FROM usersignalx WHERE acnt_id = 'eforex')) UNION (SELECT signalid FROM ( SELECT MIN(signalid) AS signalid,cmd FROM tradesignals WHERE cmd > 5 AND cmd < 9 AND signalid NOT IN (SELECT signalid FROM usersignalx WHERE acnt_id = 'eforex') AND signalid NOT IN (SELECT [login to view URL] FROM tradesignals AS earlier JOIN tradesignals AS later ON earlier.assoc_signalid = later.assoc_signalid WHERE [login to view URL] = 6 and [login to view URL] > 7) AND signalid IN (SELECT [login to view URL] FROM tradesignals AS earlier JOIN tradesignals AS later ON later.assoc_signalid = [login to view URL] WHERE [login to view URL] < 6 and [login to view URL] in (SELECT signalid FROM usersignalx WHERE acnt_id = '[acct_id]')) GROUP BY cmd ORDER BY cmd ASC) AS temp LIMIT 1)) AS final
This query is a bit inefficient. We need a qualified DBA to tune the query and recommend settings in a [login to view URL] to address this query. Coder should have training as a DBA and a MySQL expert as we have had past workers not tune the query.
Deliverables are:
1. Suggest [login to view URL]
2. Tuned query (or queries if faster)
3. MySQL Explain statements (aided by Maatkit if applicable) both of before and after to show improvement
4. Suggested structural changes such as indexes (but data needs to stay more or less as it is)
Database with tables is attached