Re: Fulltext search with order by using filesort
Posted by:
Torsten
Date: January 02, 2006 12:20PM
Thank you. This works for me. If I use heap as storage engine mysql does not use the index (with myisam it does) but its fast enough at all.
CREATE TEMPORARY TABLE pxm_tmp_search (PRIMARY KEY(tmp_id), KEY(tmp_tstmp)) ENGINE=HEAP SELECT m_id AS tmp_id, m_tstmp AS tmp_tstmp FROM pxm_board,pxm_thread,pxm_message WHERE b_id=t_boardid AND t_id=m_threadid AND b_active=1 AND MATCH (m_subject,m_body) AGAINST(".$objDb->quote($this->m_sSearchString)." IN BOOLEAN MODE);
SELECT m_id,m_threadid,t_boardid,m_subject,m_userid,m_usernickname,m_userhighlight,m_tstmp FROM pxm_tmp_search tmp,pxm_message m,pxm_thread WHERE m_id=tmp_id AND t_id=m_threadid ORDER BY tmp_tstmp DESC;
Even if i do a simple:
SELECT tmp_id FROM pxm_tmp_search ORDER BY tmp_tstmp DESC;
It does not use the index for 2000 resultrows but the filesort. I don't know why but its fine anyway.
Torsten