Please help me optimize mysql query

MySQL & MariaDB Other MySQL® Questions
1

Hello

Please help me optimize mysql query below

I have two table

table 1: “tag”

tagid int(10) unsigned NO PRI NULL auto_increment
tagtext varchar(150) NO UNI NULL
dateline int(10) unsigned NO 0

INDEX status
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
lc_tag 0 PRIMARY 1 tagid A 242988 NULL NULL BTREE
lc_tag 0 tagtext 1 tagtext A 242988 NULL NULL BTREE

table 2: tagsearch

tagid int(11) NO MUL 0
dateline int(11) NO MUL 0

Index status
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
lc_tagsearch 1 tagid 1 tagid A 222067 NULL NULL BTREE
lc_tagsearch 1 dateline 1 dateline A 2664809 NULL NULL BTREE

I use query below and get data with 3 seconds
SELECT tagsearch.tagid, tag.tagtext, COUNT(*) AS searchcount FROM tagsearch AS tagsearch INNER JOIN tag AS tag ON (tagsearch.tagid = tag.tagid) WHERE tagsearch.dateline > 1258084959 GROUP BY tagsearch.tagid, tag.tagtext ORDER BY searchcount DESC LIMIT 10

Explain

1 SIMPLE tagsearch range tagid,dateline dateline 4 NULL 696429 Using where; Using temporary; Using filesort
1 SIMPLE tag eq_ref PRIMARY PRIMARY 4 tagsearch.tagid 1 Using where

I don’t know how to optimize this query please help me

Thanks in advanced
Thanh

2

That type of query is hard to improve. It’s usually better in something like Sphinx.