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?
logicmediaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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]Connect With a Mentor 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
All Courses

From novice to tech pro — start learning today.