mysql order by vs where clause performance
I have a database with the following info:
table reports: 166211 records
table report_content: 166211 records table companies: 13188 recordsThis query takes 41.7324 sec to process:
select rc.* from `reports` r
left join `report_content` rc on rc.`report`=r.`id`
left join `companies` c on c.`id`=r.`company`
where not isnull(r.`published`) and not r.`deleted`
order by rc.`company` asc
limit 50
Where as this query takes 1.6146 sec to process:
I added and rc.company
!= ''
select rc.* from `reports` r
left join `report_content` rc on rc.`report`=r开发者_运维问答.`id`
left join `companies` c on c.`id`=r.`company`
where not isnull(r.`published`) and not r.`deleted`
and rc.`company` != ''
order by rc.`company` asc
limit 50
I have fulltext index on rc.company with a cardinality of 11872 all other clause/join fields have btree indexes (mostly primary)
Why is this so? Should i be using a fulltext/btree index on a varchar(255)?
The idea is to not have the rc.company
!= ''
FYI, the tables are MyISAM
note: The added condition doesn't change the result, it's merely to add rc.company into the conditions (this speeds up the query) and wonder if it is elegant?
Update: Thanks Frail, here are the results:
Query A:
1 SIMPLE r range published published 9 NULL 156085 Using where; Using temporary; Using filesort
1 SIMPLE rc ref report report 4 database.r.id 1
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 database.r.company 1 Using index
Query B:
1 SIMPLE rc ALL report,company NULL NULL NULL 166339 Using where; Using filesort
1 SIMPLE r eq_ref PRIMARY,published PRIMARY 4 database.rc.report 1 Using where
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 database.r.company 1 Using index
As far as I know full-text index is not for sorting. and you are sorting the result with the company column.
Let me try to explain full-text for you simply:
id company
1 "brown fox"
2 "something of fox"
your full-text index would create a btree for the words : "brown, something, fox
" so you can match against these words, but as far as I know it wont help you sort.
so if you are using full-text index to get "fox" companies, keep it. but put a btree index on company aswell for sorting purposes.
You haven't listed your table indexes but try creating the following 3 indexes
on the reports table (published,deleted,company,id)
on the report_content table (report,company)
on the companies table (id)
精彩评论