简单的SQL优化

Question

两条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_idquery time:0.1272秒
-------------------------------------------

Answer 1

第一条：不要 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'

Answer 2

试下加索引
products(products_model)
products_to_shops(products_new_model)customers_info(customers_info_date_of_last_logon)
customers(customers_info_date_account_created)

Answer 3

第二条 ：同样 不要 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

Answer 4

to arrow_gx 
怎么查询出来的结果和我的查询的结果不同?我61条.你30条.

Answer 5

用OR 真的会慢过 union all 吗?

Answer 6

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