Re: Optimizing a UNION Query
Hi - thanks for the response.
I tried the query without using subselects, but it was still very slow - (7.07 seconds to return 351 rows). But if I put a LIMIT on it, to say 10 rows, it is instantaneous. The EXPLAIN looks pretty good to me - not sure the problem is here.
Here's the output of EXPLAIN for the query without using subselects:
+----+-------------+-------+--------+----------------------+------------+---------+--------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------+------------+---------+--------------------+------+------------------------------+
| 1 | SIMPLE | g2 | range | PRIMARY,gloss_idx | gloss_idx | 47 | NULL | 2 | Using where; Using temporary |
| 1 | SIMPLE | g1 | range | PRIMARY,gloss_idx | gloss_idx | 47 | NULL | 3 | Using where |
| 1 | SIMPLE | d1 | ref | termid_idx,docid_idx | termid_idx | 9 | hailoodb.g1.termid | 13 | Using where |
| 1 | SIMPLE | di | eq_ref | PRIMARY | PRIMARY | 8 | hailoodb.d1.docid | 1 | Using index; Distinct |
| 1 | SIMPLE | d2 | ref | termid_idx,docid_idx | termid_idx | 9 | hailoodb.g2.termid | 13 | Using where; Distinct |
+----+-------------+-------+--------+----------------------+------------+---------+--------------------+------+------------------------------+
And here's the SHOW create table:
SHOW CREATE TABLE glossary:
| glossary | CREATE TABLE `glossary` (
`termid` bigint(20) unsigned NOT NULL auto_increment,
`word` varchar(100) NOT NULL,
`lang` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`termid`),
UNIQUE KEY `gloss_idx` (`word`(15),`termid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
SHOW CREATE TABLE docref:
| docref | CREATE TABLE `docref` (
`termid` bigint(20) unsigned default NULL,
`docid` bigint(20) unsigned default NULL,
`loc` int(10) unsigned default NULL,
`freq` int(10) unsigned NOT NULL default '0',
KEY `termid_idx` (`termid`),
KEY `docid_idx` (`docid`),
KEY `loc_idx` (`loc`),
KEY `freq_idx` (`freq`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii
SHOW CREATE TABLE docIndex:
| docIndex | CREATE TABLE `docIndex` (
`docid` bigint(20) unsigned NOT NULL auto_increment,
`title` varchar(200) NOT NULL,
`doc` mediumblob,
`time` datetime default NULL,
PRIMARY KEY (`docid`),
KEY `doc_idx` (`title`(45),`docid`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |