mysql两种查询方式对比--子查询与join查询

下面两个例子分别是：join查询与 子查询，网易的书上说 join查询要比 嵌套子查询要快，因为要用到临时表。。。

 

但我在mysql下执行结果却相反（如下）,

 

Mysql 5.045

linux平台

 

 

mysql> explain SELECT  *  FROM material JOIN groupmaterial on  (material.id=groupmaterial.fid AND  groupmaterial.gid=1)  where (material.title like '%' OR material.tag LIKE '%' OR material.descript LIKE '%')  GROUP BY material.id  ORDER BY material.created ASC /G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: groupmaterial
         type: ref
possible_keys: groupmaterial_fid,groupmaterial_gid
          key: groupmaterial_gid
      key_len: 4
          ref: const
         rows: 17
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: material
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: material.groupmaterial.fid
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

ERROR:
No query specified

 

 

mysql> explain SELECT  *  FROM material WHERE id IN (SELECT DISTINCT fid FROM groupmaterial WHERE gid=1)  AND (material.title like '%' OR material.tag LIKE '%' OR material.descript LIKE '%')  GROUP BY id  ORDER BY created ASC /G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: material
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 30
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: groupmaterial
         type: index_subquery
possible_keys: groupmaterial_fid,groupmaterial_gid
          key: groupmaterial_fid
      key_len: 4
          ref: func
         rows: 4
        Extra: Using where
2 rows in set (0.00 sec)

 

 

 

## 然后 写个脚本 在django环境下运行，两个语句 分别 执行 1000次。

 

[root@wps-node2 MaterialServer]# python2.5 test_mysqlcmd.py
1
search1 user time:  0:00:01.567074    # 这里是join查询
1
search2 user time:  0:00:01.432829    # 这里是子查询
[root@wps-node2 MaterialServer]# python2.5 test_mysqlcmd.py
1
search1 user time:  0:00:01.563794
1
search2 user time:  0:00:01.426497

 

 

 

脚本 内容如下：

#!/usr/bin/env python
#-*- coding:utf-8 -*-
#by TooNTonG at 2009/5/19

 

import settings
from django.core.management.__init__ import setup_environ
setup_environ(settings)

from django.db import connection
from django.db.models import Q
from datetime import datetime
import random

cursor = connection.cursor()
def search1(uname='', tag='', descript=''):

    sqlCmd = "SELECT * FROM material JOIN groupmaterial on  (material.id=groupmaterial.fid AND  groupmaterial.gid=1)  where (material.title like '%%"+ tag +"%%' OR material.tag LIKE '%%"+ tag+"%%' OR material.descript LIKE '%%" + tag+"%%')  GROUP BY material.id  ORDER BY material.created ASC"      #

 

    cursor.execute(sqlCmd)
    rs = cursor.fetchall()

    return rs

 

 

def search2(uname='', tag='', descript=''):
    sqlCmd = "SELECT * FROM material WHERE id IN (SELECT DISTINCT fid FROM groupmaterial WHERE gid=1)  AND (material.title like '%%"+tag+"%%' OR material.tag LIKE '%%"+tag+"%%' OR material.descript LIKE '%%"+tag+"%%')  GROUP BY id  ORDER BY created ASC "   #

    cursor.execute(sqlCmd)
    rs = cursor.fetchall()

    return rs

 

SUM = 1000
def main():

    user =1
    tag = 't'
    desc = 'd'

 

    begin = datetime.now()
    for i in range(SUM):
        search1(user, tag, desc)
    print len(search1(user, tag, desc))
    end = datetime.now()
    print "search1 user time: ", end - begin

    begin = datetime.now()
    for i in range(SUM):
        search2(user, tag, desc)
    print len(search2(user, tag, desc))
    end = datetime.now()
    print "search2 user time: ", end - begin

 

if __name__ == "__main__":
    main()
    pass