Re: slow performance due to OR condition on separate tables
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