我遇到了一个典型的性能问题，即“or”操作符没有在mysql中使用任何索引：

SELECT sms.smsID, sms.phonenumber, sms.text, date, mbr.name, mbr.prename 
FROM sms INNER JOIN (SELECT MAX(smsID) as smsID, phonenumber FROM sms 
group by phonenumber) sms2 ON sms.phonenumber = sms2.phonenumber AND 
sms.smsID = sms2.smsID
LEFT JOIN mbr ON mbr.telephone = sms.phonenumber OR mbr.mobilphone = 
sms.phonenumber where sms.smsmodem = 'GSM1'
order by date desc LIMIT 0,50

查询应该返回最后50条收到的短信，但每个电话号码只能返回一条。然后将其与mbr表左连接，以查找电话号码是否连接到mbr表中的人名。无论如何，在mbr表中，电话号码可能在“telephone”或“mobilephone”列中，因此我们必须检查两者。where子句中的or运算符使查询超过20秒，因为它不使用mbr.telephone和mbr.mobilephone的索引。
找到的解决方案要么像这样做一个左连接构造：sql-left-join with or operator（mysql），要么像这样的联合构造：mysql-join-query with or子句非常慢
但这两种情况我都不能用。left join构造将返回四列，而不是两列：

SELECT sms.smsID, sms.phonenumber, sms.text, date, mbr.name,    
mbr.prename, mbr2.name, mbr2.prename FROM sms INNER JOIN (SELECT 
MAX(smsID) as smsID, phonenumber FROM sms group by phonenumber) sms2 ON 
sms.phonenumber = sms2.phonenumber AND sms.smsID = sms2.smsID LEFT JOIN 
mbr ON mbr.telephone = sms.phonenumber left join mbr as mbr2 ON 
mbr2.mobilphone = sms.phonenumber where sms.smsmodem = 'GSM1' order by 
date desc LIMIT 0,50

union解决方案将只返回两列，但将两次返回每个收到的消息，一次返回发件人的名称，一次不返回（因为一个select将在mbr中找到相应的行，另一个不会）：

SELECT sms.smsID, sms.phonenumber, sms.text, date, mbr.name,    
mbr.prename, mbr2.name, mbr2.prename FROM sms INNER JOIN (SELECT 
MAX(smsID) as smsID, phonenumber FROM sms group by phonenumber) sms2 ON 
sms.phonenumber = sms2.phonenumber AND sms.smsID = sms2.smsID LEFT JOIN 
mbr ON mbr.telephone = sms.phonenumber where sms.smsmodem = 'GSM1'
UNION SELECT sms.smsID, sms.phonenumber, sms.text, date, mbr.name,    
mbr.prename, mbr2.name, mbr2.prename FROM sms INNER JOIN (SELECT 
MAX(smsID) as smsID, phonenumber FROM sms group by phonenumber) sms2 ON 
sms.phonenumber = sms2.phonenumber AND sms.smsID = sms2.smsID LEFT JOIN 
mbr ON mbr.mobilephone = sms.phonenumber where sms.smsmodem = 'GSM1'
order by date desc LIMIT 0,50

不知怎的，我在想，这些“或”运算符问题在2016年仍然存在。。。