• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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)
  )
0
pointeman
Asked:
pointeman
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
check out the EXPLAIN PLAN for the SELECT part of the query.
the main reason will be the NOT IN ( subselect )..

I would guess you don't have any index on "logged" column of Secured table ...
do that, and change also your query to:

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 NOT EXISTS( Select null From Secured s WHERE m.Logged = s.Logged )
  )

Open in new window

0
 
pointemanAuthor Commented:
I'm currently re-imaging the test computer in question. I'll let you know...
0
 
pointemanAuthor Commented:
Oops, sorry, didn't respond to you code answer. Okay. tried it without any better results...
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
explain plan, please ...
0
 
pointemanAuthor Commented:
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.
0
 
pointemanAuthor Commented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now