SQL Running Very Very Slowly?
I run this query on MSSQL successfully, however it does not run very well on MySql (query is being executed..). This is the first time MySQL has choked running a query. Also tried this on different computers both remote (LAN) and local db connections. MySQL Query Browser is not hanging or returning errors, just runs and runs until I cancel it!
Q. How can I increase code efficiency for MySQL?
Purpose: Insert only new records (no matching 'Logged' datetime's) into Secured table from the Main table Where Port (Ports table) is marked Secure
Also not using 'Group By' hoping it would speed things up.
Secured = tinyint(1)
Logged = datetime
Priority = varchar(50)
Port = varchar(10)
Insert into Secured (Cleared,Logged,Priority, Port)
(SELECT 0, Logged,Priority, Port
FROM Main m
JOIN Ports p on p.Port = m.Port
Where p.Secure = '1'
And m.Logged Not In (Select Logged From Secured)
)
Q. How can I increase code efficiency for MySQL?
Purpose: Insert only new records (no matching 'Logged' datetime's) into Secured table from the Main table Where Port (Ports table) is marked Secure
Also not using 'Group By' hoping it would speed things up.
Secured = tinyint(1)
Logged = datetime
Priority = varchar(50)
Port = varchar(10)
Insert into Secured (Cleared,Logged,Priority, Port)
(SELECT 0, Logged,Priority, Port
FROM Main m
JOIN Ports p on p.Port = m.Port
Where p.Secure = '1'
And m.Logged Not In (Select Logged From Secured)
)
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oops, sorry, didn't respond to you code answer. Okay. tried it without any better results...
explain plan, please ...
ASKER
Okay, here's the deal. I discovered that we are running MySQL 5.5.9 ... So, we re-imaged the drive and installed our ole reliabe 5.5.19 and everything is working fine.
NOTE: Before re-imaging, we reconfigured the 5.5.9 instance and the code I posted worked quick. However a few hours running and MySQL began to choke on the same code. I'm not sure where I download 5.5.9 anyway.. All is well.
NOTE: Before re-imaging, we reconfigured the 5.5.9 instance and the code I posted worked quick. However a few hours running and MySQL began to choke on the same code. I'm not sure where I download 5.5.9 anyway.. All is well.
ASKER
Excellent code example. I must note the problem was using MySQL 5.5.9 instead of 5.5.19 which we never have problems with.
Thanks...
Thanks...
ASKER