开发者

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 records

This 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)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜