SQL forces the specified index to speed up queries

Today encountered a query problem, add a query parameter caused by query super-times yellow, after the company DBA Improvement, up posture. Now send to share with you! ~

SELECT m.* from Tb_usersitegroup u with (NOLOCK), Message.dbo.View_Message_8 m with (NOLOCK) WHERE  m.fromuserid = U. UserID and   U.adminid =880982 and m.state=1 and  m.touserid=0 and U.siteid = 8ORDER by  m.time DESC

This is the original SQL, with the extra U. Adminid = 880982 parameter causes the query to time out.

The query followed by with (NOLOCK) can theoretically speed up the query by 33%, but may cause dirty reads (Baidu, you know)

This is the improved SQL:

SELECT  m.*from    tb_usersitegroup u with (nolock,index=ix_tb_usersitegroup_siteid)  JOIN       Message.dbo.View_Message_8 m with (NOLOCK) on    M.fromuserid = U.userid and        m.state = 1 and        m.touserid = 0        and U.siteid = 8 and        U.adminid = 880982ORDER by M.time DESC

From the previous query time 2 minutes to become now seconds out ... Creates a new Ix_tb_usersitegroup_siteid index and forces the query index to be specified.

The join connection form is changed from the previous comma connection (no known what is called).

Asked DBA,DBA that the join connection automatically searches for the optimal, best-fit index for lookups, whereas a comma connection is a matching lookup based on the Where condition. There is a functional loss between

These are my vague memories of things, the DBA said the specific are not clear, may not be accurate, welcome you to add the discussion.

SELECT m.* from Tb_usersitegroup u w (nolock,index=ix_tb_usersitegroup_siteid), Message.dbo.View_Message_8 m with ( NOLOCK) WHERE  M.fromuserid = U.userid and U.adminid =880982 and m.state=1 and M.touserid=0 and  U.siteid = 8ORD ER by  M.time DESC

This SQL is the result of the original SQL plus the specified index.
Query time is also seconds out ...
In terms of data volume and time, the essential difference between join and comma connections is not visible for the moment. Follow-up study is followed.

For the time being so many, write down for later reference, also for everyone reference study.

Welcome to the Danale to enlighten them. Add the wrong place ...

SQL forces the specified index to speed up queries