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

Optimizing two MySQL queries

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
logicmedia
Asked:
logicmedia
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
logicmediaAuthor Commented:
Angellll: It takes the query down to about half a second which is much better. Thanks a lot...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, so now the second part:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
logicmediaAuthor Commented:
Thanks a lot... I can't wait to get better at this ;-)
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.

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