SELECT a.accountid, a.accountname, f.websiteurl, c.advertisername, c.trackersystem, c.rank, a.registerdate, m.statusname, a.statusid, l.bankaccount, b.username, b.name, a.accountid
FROM advertiser_account a
LEFT JOIN user b ON ( a.userid = b.userid )
LEFT JOIN advertiser_profile c ON ( a.advertiserid = c.advertiserid )
LEFT JOIN ssn d ON ( a.ssnid = d.ssnid )
LEFT JOIN phone e ON ( a.phoneid = e.phoneid )
LEFT JOIN website f ON ( a.websiteid = f.websiteid )
LEFT JOIN name g ON ( a.nameid = g.nameid )
LEFT JOIN address k ON ( a.addressid = k.addressid )
LEFT JOIN bank l ON ( a.bankid = l.bankid )
LEFT JOIN website_email n ON ( a.emailid = n.emailid )
LEFT JOIN advertiser_account_status m ON ( a.statusid = m.statusid )
ORDER BY m.statusname, c.rank DESC , a.accountname DESC
LIMIT 0 , 50
ORDER BY m.statusname, c.rank DESC , a.accountname DESC
这个组合索引怎建,都是不同表排序, ALTER TABLE advertiser_account ADD INDEX advertiser_account_name(accountname); 增加了个索引ORDER BY m.statusname, c.rank DESC , a.accountname DESC
还是对这三个表都到这每个表建索引到这每个表
m.statusname表建索引
c.rank 表建索引
a.accountname 表建索引
不太知怎提速度了。
FROM advertiser_account a
LEFT JOIN user b ON ( a.userid = b.userid )
LEFT JOIN advertiser_profile c ON ( a.advertiserid = c.advertiserid )
LEFT JOIN ssn d ON ( a.ssnid = d.ssnid )
LEFT JOIN phone e ON ( a.phoneid = e.phoneid )
LEFT JOIN website f ON ( a.websiteid = f.websiteid )
LEFT JOIN name g ON ( a.nameid = g.nameid )
LEFT JOIN address k ON ( a.addressid = k.addressid )
LEFT JOIN bank l ON ( a.bankid = l.bankid )
LEFT JOIN website_email n ON ( a.emailid = n.emailid )
LEFT JOIN advertiser_account_status m ON ( a.statusid = m.statusid )
ORDER BY m.statusname, c.rank DESC , a.accountname DESC
LIMIT 0 , 50
ORDER BY m.statusname, c.rank DESC , a.accountname DESC
这个组合索引怎建,都是不同表排序, ALTER TABLE advertiser_account ADD INDEX advertiser_account_name(accountname); 增加了个索引ORDER BY m.statusname, c.rank DESC , a.accountname DESC
还是对这三个表都到这每个表建索引到这每个表
m.statusname表建索引
c.rank 表建索引
a.accountname 表建索引
不太知怎提速度了。
————————————————————————————————
基于CSDN论坛提供的插件扩展功能,自己做了个签名档工具,分享给大家,欢迎技术交流 :)[/size]
---------------------------------------------------------------------
1.order by不同表的字段只能使用filesort,即explain出来,extra列那里有Using filesort
2.即使是order by同一表的几个字段,彼此排序不统一(既有asc也有desc)也使用不到索引两项你都占了,而且你语句里并无where条件语句,效率必然和左表(left join前面的表)记录数成反比
其余的用explain分析就行了,尽量做到最合适的即可.