MySQL Forums :: Newbie :: Slow Query for Alphabetically Adjacent Terms


Advanced Search

Slow Query for Alphabetically Adjacent Terms
Posted by: Chad Cluff ()
Date: March 23, 2010 01:29PM

I have a large table (19,112,023 rows) that contains a text column that users search. One of the features our users have requested is the ability to get an alphabetical list of terms that are adjacent to their search term.

The table:
DROP TABLE IF EXISTS `textinfo`.`master_heading`;
CREATE TABLE  `textinfo`.`master_heading` (
  `rec_id` int(10) unsigned NOT NULL,
  `field_id` smallint(6) NOT NULL,
  `heading_id` smallint(6) NOT NULL,
  `heading_seq` tinyint(3) unsigned NOT NULL,
  `field_tag` smallint(6) NOT NULL,
  `heading` varchar(1000) NOT NULL,
  `norm_type` tinyint(3) unsigned NOT NULL,
  KEY `headIdx` (`heading`(333)),
  KEY `rec_id` (`rec_id`),
  FULLTEXT KEY `heading` (`heading`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I've come up with this query:
(SELECT rec_id, heading as head FROM master_heading m WHERE heading > "SEARCH TERM" ORDER BY heading ASC LIMIT 5)
UNION
(SELECT rec_id, heading as head FROM master_heading m2 WHERE heading < "SEARCH TERM" ORDER BY heading DESC LIMIT 5)
ORDER BY head;

Which does return the desired results, it just tends to take approximately 120 - 160 seconds.

Explain gives me this:
1, 'PRIMARY', 'm', 'ALL', 'headIdx,heading', '', '', '', 19112023, 'Using where; Using filesort'
2, 'UNION', 'm2', 'range', 'headIdx,heading', 'headIdx', '1001', '', 742279, 'Using where; Using filesort'
, 'UNION RESULT', '<union1,2>', 'ALL', '', '', '', '', , 'Using filesort'

Is there a way I can better optimize this? Our users would prefer quicker results.

Thanks for any help.

Options: ReplyQuote


Subject Written By Posted
Slow Query for Alphabetically Adjacent Terms Chad Cluff 03/23/2010 01:29PM
Re: Slow Query for Alphabetically Adjacent Terms Rick James 03/24/2010 08:08PM
Re: Slow Query for Alphabetically Adjacent Terms Chad Cluff 03/30/2010 01:56PM
Re: Slow Query for Alphabetically Adjacent Terms Rick James 03/30/2010 10:02PM
Re: Slow Query for Alphabetically Adjacent Terms Chad Cluff 03/31/2010 03:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.