MySQL Forums
Forum List  »  Full-Text Search

Re: slow performance due to OR condition on separate tables
Posted by: Jose-Miguel Pulido
Date: December 03, 2009 03:43AM

Hi Rick,

Thanks alot for your reply, and apologies for the delay in the reply. Your suggestion did the trick. Replacing the OR with UNION DISTINCTs had a significant improvement in performance.

I also tried UNION ALL instead of UNION DISTINCT, and performance was very similar.

Furthermore, in addition to:

SELECT *
FROM product p,
( the union above ) u
WHERE p.product_id = u.product_id

I also tried:

SELECT *
FROM product p
WHERE p.product_id IN ( the union above )


From the output you requested to analyze performance:

<code>
mysql> show variables like '%buffer%';
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| bdb_log_buffer_size | 262144 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------------+---------+
</code>

I mostly have innodb tables, except for a couple of mysam tables on where I
performn full text searching. Details of these two tables below


<code>
mysql> show create table coupon_descriptions;
...
| coupon_descriptions | CREATE TABLE `coupon_descriptions` (
`coupon_id` int(10) unsigned NOT NULL,
`language_id` smallint(6) NOT NULL,
`conditions` varchar(1024) NOT NULL,
`description` varchar(1024) default NULL,
`savings` text,
`name` varchar(128) default NULL,
PRIMARY KEY (`coupon_id`,`language_id`),
FULLTEXT KEY `coupon_descriptions_name_and_description_idx` (`name`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
</code>

<code>
mysql> show create table search_tags;
...
| search_tags | CREATE TABLE `search_tags` (
`search_tag_id` int(11) NOT NULL auto_increment,
`language_id` smallint(6) NOT NULL,
`tag` varchar(128) default NULL,
`generator` enum('advertiser','qporama','consumer','partner') default 'qporama',
PRIMARY KEY (`search_tag_id`),
UNIQUE KEY `TagIsUnique_idx` (`tag`),
FULLTEXT KEY `tag` (`tag`),
FULLTEXT KEY `search_tags_tag_idx` (`tag`)
) ENGINE=MyISAM AUTO_INCREMENT=1352 DEFAULT CHARSET=utf8
</code>


Thanks again,

JM

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: slow performance due to OR condition on separate tables
3240
December 03, 2009 03:43AM


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.