Solved

Optimizing two MySQL queries

Posted on 2008-06-11
5
198 Views
Last Modified: 2010-04-21
I have two simple SQL queries that I have no idea how to optimize

The first one is pretty simple:
SELECT * FROM tags WHERE tag IN (SELECT DISTINCT(tag) FROM tags WHERE datatype = 'post' AND param1 = '9525')

The table has an index on the "tag" column. It easily takes 3 seconds. Can it be optimized in any way?

The other one I think is simple (I just have limited SQL knowledge). My way of implementing it is with a loop:

SQLStr = "SELECT * FROM blogusers WHERE userid = '" & curUserID & "'"
set rstDB = cnnDB.Execute(SQLStr)
Do While Not rstDB.EOF
    tmpBlogID = rstDB.Fields("blogid").Value
    SQLStr = "UPDATE blogstats SET myself = 'yes' WHERE blogid = '" & tmpBlogID & "' AND IP = '" & ipadr & "'"
    cnnDB.Execute(SQLStr)
    rstDB.MoveNext
Loop

Can this be combined to one UPDATE SQL query?
0
Comment
Question by:logicmedia
  • 3
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
>SELECT * FROM tags WHERE tag IN (SELECT DISTINCT(tag) FROM tags WHERE datatype = 'post' AND param1 = '9525')
might work faster like this:
SELECT * FROM tags t WHERE EXISTS (SELECT NULL FROM tags i WHERE i.tag = t.tag and i.datatype = 'post' AND i.param1 = '9525')

with another (1) index on datatype + param1 + tag (all 3 fields, in that order, on the index)
0
 

Author Comment

by:logicmedia
Comment Utility
Angellll: It takes the query down to about half a second which is much better. Thanks a lot...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
ok, so now the second part:
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
oops...

just do this:
SQLStr = "UPDATE blogstats bs SET myself = 'yes'  "

SQLStr = SQLStr & " WHERE bs.IP = '" & ipadr & "' "

SQLStr = SQLStr & " AND bs.blogid IN ( SELECT blogid FROM bloguser WHERE userid = '" & curUserID & "' ) "
 

cnnDB.Execute(SQLStr)

Open in new window

0
 

Author Closing Comment

by:logicmedia
Comment Utility
Thanks a lot... I can't wait to get better at this ;-)
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now