MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing a UNION Query
Posted by: Charles Salvia
Date: August 24, 2006 12:12PM

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 |

Options: Reply• Quote


Subject
Views
Written By
Posted
6584
August 23, 2006 08:16PM
2850
August 24, 2006 04:59AM
2865
August 24, 2006 05:28AM
Re: Optimizing a UNION Query
2913
August 24, 2006 12:12PM
5627
August 25, 2006 06:14AM


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.