1

I have two tables t_core_blueprints,t_ci.

CREATE TABLE `t_core_blueprints` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,

  `last_modified_on` bigint(20) NOT NULL,
  `parent_blueprint` int(11) DEFAULT NULL,
  `icon` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `is_ci_part` bit(1) DEFAULT NULL,
  `archive` bit(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `is_ci_part` (`is_ci_part`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `t_ci` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci,
  `record_id` text COLLATE utf8_unicode_ci,
  `last_modified_on` bigint(20) DEFAULT NULL,
  `archive` bit(1) DEFAULT NULL,
  `blueprint_id` int(11) DEFAULT NULL,
  `license_group` int(11) DEFAULT NULL,
  `s39` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK362C11874EFF37` (`blueprint_id`),
  KEY `record_id_index` (`record_id`(64)) USING BTREE,
  KEY `processed_remote_ci` (`archive`,`s39`,`blueprint_id`),
  KEY `lastModified` (`last_modified_on`),
  KEY `Test1` (`archive`,`last_modified_on`),
  KEY `Test2` (`blueprint_id`,`archive`,`last_modified_on`),
  CONSTRAINT `FK362C11874EFF37` FOREIGN KEY (`blueprint_id`) REFERENCES `t_core_blueprints` (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I am running two queries


SELECT tci0_.id AS col_0_0 
FROM t_ci tci0_ 
CROSS JOIN t_core_blueprints tmblueprin1_ 
WHERE tci0_.blueprint_id=tmblueprin1_.id 
  AND tmblueprin1_.is_ci_part=0 
  AND tci0_.archive=0;

EXPLAIN Plan for this query:-

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tci0_
   partitions: NULL
         type: ref
possible_keys: PRIMARY,AssetID,lastModified,Test1,Test2
          key: processed_remote_ci
      key_len: 2
          ref: const
         rows: 889058
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tmblueprin1_
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,is_ci_part
          key: PRIMARY
      key_len: 4
          ref: emdb_1547105779479.tci0_.blueprint_id
         rows: 1
     filtered: 65.71
        Extra: Using where

SELECT tci0_.id AS col_0_0 
FROM t_ci tci0_ 
CROSS JOIN t_core_blueprints tmblueprin1_ 
WHERE tci0_.blueprint_id=tmblueprin1_.id 
  AND tmblueprin1_.is_ci_part=0 
  AND tci0_.archive=0 
  AND tmblueprin1_.name LIKE '%%';

EXPLAIN Plan is:-

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tmblueprin1_
   partitions: NULL
         type: ref
possible_keys: PRIMARY,is_ci_part
          key: is_ci_part
      key_len: 2
          ref: const
         rows: 69
     filtered: 11.11
        Extra: Using where; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tci0_
   partitions: NULL
         type: ref
possible_keys: PRIMARY,AssetID,processed_remote_ci,lastModified,Test1,Test2
          key: Test2
      key_len: 7
          ref: emdb_1547105779479.tmblueprin1_.id,const
         rows: 93585
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

I am not able to understand just adding a condition tmblueprin1_.name LIKE '%%' in the second query made its execution faster. Can you explain what is happening here?

  • Do you really need the DISTINCT? It looks like you don't and removing it would likely improve performance. – ypercubeᵀᴹ May 29 at 12:42
  • @ypercubeᵀᴹ DISTINCT is needed for the requirement. – dragon May 29 at 14:30
  • 1
    Why is it needed? You are selecting tci0_.id which is the primary key. Are you saying you get different results if you remove the DISTINCT? – ypercubeᵀᴹ May 29 at 14:39
  • @ypercubeᵀᴹ I understood the mistake. I have edited the query . Thank you – dragon May 29 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.