Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
  1. My query is not using indexes on third table(pci promotion_coupon_images) .
  2. i was tried with using index and force index in query but there is no change in result.
  3. any body please suggest me how this query will us indexes on table (pci promotion_coupon_images)

query

select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from
promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci on pc.coupon_id = convert(pci.image_key,unsigned integer)
where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;

Table structure:

mysql>  show create table promotion_coupon_types\G
*************************** 1. row ***************************
       Table: promotion_coupon_types
Create Table: CREATE TABLE `promotion_coupon_types` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `coupon_type_id` int(11) NOT NULL,
  `pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `coupon_type_code` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `description` longtext COLLATE latin1_bin,
  `type` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `validate_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `calculate_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `remove_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `coupon_constructor_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `alter_subtotal` int(11) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_1505` (`coupon_type_id`),
  UNIQUE KEY `idx_1711` (`coupon_type_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql>  show create table promotion_coupons\G
*************************** 1. row ***************************
       Table: promotion_coupons
Create Table: CREATE TABLE `promotion_coupons` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `coupon_id` int(11) DEFAULT NULL,
  `promotion_id` int(11) DEFAULT NULL,
  `coupon_type_id` int(11) NOT NULL,
  `pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `description` longtext COLLATE latin1_bin,
  `accept_text` longtext COLLATE latin1_bin,
  `active` int(11) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `value` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `min_value` decimal(6,2) DEFAULT NULL,
  `needs_coupon` int(11) DEFAULT NULL,
  `display` int(11) DEFAULT NULL,
  `auto_apply` int(11) DEFAULT NULL,
  `applicable_start_date` datetime DEFAULT NULL,
  `applicable_end_date` datetime DEFAULT NULL,
  `tax_after_discount` int(11) DEFAULT NULL,
  `delivery_type` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `items_list` longtext COLLATE latin1_bin,
  `display_options` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_960` (`coupon_id`),
  KEY `idx_1067` (`start_date`),
  KEY `idx_1299` (`promotion_id`),
  KEY `idx_820` (`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql>  show create table promotion_coupon_images\G
*************************** 1. row ***************************
       Table: promotion_coupon_images
Create Table: CREATE TABLE `promotion_coupon_images` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `image_key` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `data` longtext COLLATE latin1_bin,
  `content_type` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_939` (`image_key`),
  KEY `idx_pci_cmp_n1` (`pretty_name`,`width`,`height`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

normal query explain plan

+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

use index explain plan:

mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci USE INDEX(idx_pci_cmp_n1)  on pc.coupon_id = convert(pci.image_key,unsigned integer)  where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)



mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci USE INDEX(idx_939)  on pc.coupon_id = convert(pci.image_key,unsigned integer)  where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

force index explain plan:

mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci force INDEX(idx_939)  on pc.coupon_id = convert(pci.image_key,unsigned integer)  where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci force INDEX(idx_pci_cmp_n1)  on pc.coupon_id = convert(pci.image_key,unsigned integer)  where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | NULL          | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

if i removed convert integer also no change in result

mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci on pc.coupon_id = pci.image_key where pc.coupon_type_id=pct.coupon_type_id  and pc.promotion_id=17; +----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
|  1 | SIMPLE      | pc    | ref    | idx_1299      | idx_1299 | 5       | const                 |  356 | Using where |
|  1 | SIMPLE      | pct   | eq_ref | idx_1505      | idx_1505 | 4       | cms.pc.coupon_type_id |    1 |             |
|  1 | SIMPLE      | pci   | ALL    | idx_939       | NULL     | NULL    | NULL                  |    1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)

please find table structure with new column image_key_int on promotion_coupon_images for casting also added indexes.

mysql> show create table promotion_coupon_images\G
*************************** 1. row ***************************
       Table: promotion_coupon_images
Create Table: CREATE TABLE `promotion_coupon_images` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `image_key` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `data` longtext COLLATE latin1_bin,
  `content_type` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `image_key_int` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `idx_939` (`image_key`),
  KEY `idx_pci_cmp_n1` (`pretty_name`,`width`,`height`) USING BTREE,
  KEY `idx_img_key_n1` (`image_key_int`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)
share|improve this question
2  
Having to use convert on image_key makes it very hard to use the index (the DB would need to know how to reverse the convert process and this is a bit ambiguous). Can't you change the column type of image_key to be integer? – Eelke Feb 19 at 6:35
I was removed convert process but there is no change in result still the result is same.please find modified result in the topic. – ashuthosh Feb 19 at 8:43
You should not combine implicit and explicit joins either. I don't know about mysql but I have seen incorrect results from that in SQl server as it confuses the optimizer. Of course you should not use implicit joins period as they are a SQL antipattern and were replaced in the last century. – HLGEM Feb 19 at 16:05

1 Answer

up vote 3 down vote accepted

The problem is pc.coupon_id = convert(pci.image_key,unsigned integer), as eelke said. Having an implicit cast negates the use of an index on MySQL. Unfortunately I couldn't find anything about the conversion from varchar to int and indexes in the official MySQL documentation.

You can read more on the subject here.

The solution would be to either alter the table promotion_coupon_images and convert pci.image_key to unsigned int before join, or add an other column in the table named image_key_int (or however you wish), of type int and update it's content with convert(pci.image_key,unsigned integer).

share|improve this answer
1  
could you please update the mysql> show create table promotion_coupon_images\G Also did you create an index on the new collumn, an use that column in the join ? – clickstefan Feb 19 at 9:47
very very thanks @clickstefan it was working fine. – ashuthosh Feb 19 at 10:08
1  
great, glad that it helped. – clickstefan Feb 19 at 10:29
1  
Data fiels you intend to join should be designed from the start to use the same datatype. Anytime you are converting in a join you are harming your performance. – HLGEM Feb 19 at 16:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.