简单的SQL优化

[剑心永远OK]

两条SQL语句。感觉有点慢。大家帮忙优化下。。
第一条:

SELECT p.products_id
FROM products p, products_to_shops p2s
WHERE (
p.products_model
IN (
'NIB037', 'NAM006', 'DKY004', 'NDE022', 'CSP004SU', 'DPS003', 'TMK030', 'ENB005L', 'VJV04', 'CMN001L'
)
OR p2s.products_new_model
IN (
'NIB037', 'NAM006', 'DKY004', 'NDE022', 'CSP004SU', 'DPS003', 'TMK030', 'ENB005L', 'VJV04', 'CMN001L'
)
)
AND p.products_id = p2s.products_id
AND shops_id = '7'

query time:1.7163 秒
------------------------------------------------------------------------------
第二条

SELECT c.customers_firstname, c.customers_lastname, c.customers_email_address
				FROM customers_info ci, customers c
				WHERE (
				(
				ci.customers_info_date_of_last_logon
				BETWEEN '2008-07-07 01:49:00'
				AND '2008-07-07 21:48:00'
				)
				OR (
				ci.customers_info_date_account_created
				BETWEEN '2008-07-07 01:49:00'
				AND '2008-07-07 21:48:00'
				)
				)
				AND c.customers_id = ci.customers_info_id

query time:0.1272秒
-------------------------------------------

[zhangyanxxxx]

尽量不要使用OR

[ojuju10]

建立索引，优化下查询语句！

[wynlc]

MARK

[zoffor]

把这两个条件放前面会不会好点？
AND p.products_id = p2s.products_id
AND shops_id = '7'

[hery2002]

貌似优化余地不大..
查询时间还可以接受啊～

[arrow_gx]

第一条：不要 or 条件，分两次查询也比你一个 or 条件要快

SELECT p.products_id
FROM products p, products_to_shops p2s
WHERE p.products_model
IN (
'NIB037', 'NAM006', 'DKY004', 'NDE022', 'CSP004SU', 'DPS003', 'TMK030', 'ENB005L', 'VJV04', 'CMN001L'
) AND p.products_id = p2s.products_id
AND shops_id = '7'
Union all
SELECT p.products_id
FROM products p, products_to_shops p2s
WHERE p2s.products_new_model
IN (
'NIB037', 'NAM006', 'DKY004', 'NDE022', 'CSP004SU', 'DPS003', 'TMK030', 'ENB005L', 'VJV04', 'CMN001L'
)
AND p.products_id = p2s.products_id
AND shops_id = '7'

[昵称被占用了]

试下加索引
products(products_model)
products_to_shops(products_new_model)

customers_info(customers_info_date_of_last_logon)
customers(customers_info_date_account_created)

[arrow_gx]

第二条 ：同样 不要 or 条件，分两次查询也比你一个 or 条件要快

SELECT c.customers_firstname, c.customers_lastname, c.customers_email_address
                FROM customers_info ci, customers c
                WHERE 
                (ci.customers_info_date_of_last_logon
                BETWEEN '2008-07-07 01:49:00'
                AND '2008-07-07 21:48:00')
                AND c.customers_id = ci.customers_info_id
                
union all --连接两个查询结果

SELECT c.customers_firstname, c.customers_lastname, c.customers_email_address
                FROM customers_info ci, customers c
                WHERE
                (ci.customers_info_date_account_created
                BETWEEN '2008-07-07 01:49:00'
                AND '2008-07-07 21:48:00')
                AND c.customers_id = ci.customers_info_id

[剑心永远OK]

[Quote=引用 1 楼 hery2002 的回复:]
貌似优化余地不大..
查询时间还可以接受啊～
[/Quote]
能快一秒是一秒啊..

[wwd252]

顶一下

[剑心永远OK]

[Quote=引用 3 楼 Haiwer 的回复:]
试下加索引
products(products_model)
products_to_shops(products_new_model)

customers_info(customers_info_date_of_last_logon)
customers(customers_info_date_account_created)
[/Quote]
已经都加了..

[剑心永远OK]

to arrow_gx
怎么查询出来的结果和我的查询的结果不同?

我61条.你30条.

[Garnett_KG]

帖出执行计划，帖出表结构，执行结果影响的行数。

[剑心永远OK]

用OR 真的会慢过 union all 吗?