Solved

Optimizing two MySQL queries

Posted on 2008-06-11
5
201 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
ID: 21758104
>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
ID: 21758122
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]
ID: 21758217
ok, so now the second part:
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21758235
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
ID: 31466071
Thanks a lot... I can't wait to get better at this ;-)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 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