Announcement

Announcement Module
Collapse
No announcement yet.

Please help me optimize mysql query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Please help me optimize mysql query

    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.

    Comment

    Working...
    X