mysql> show create table TopsDataDrug.dbo_DRUG_Brand; +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | dbo_DRUG_Brand | CREATE TABLE `dbo_DRUG_Brand` ( `Brand_ID` varchar(10) NOT NULL default 'CREATE DEF', `Brand_Description` varchar(100) NOT NULL default 'CREATE DEFAULT D_EmptyString AS ''''\r\n', `Brand_RecordState` smallint(6) NOT NULL default '0', `Brand_IsGeneric` tinyint(1) NOT NULL default '0', `Brand_Code` varchar(11) NOT NULL default 'CREATE DEFA', `Ingredient_ID` varchar(10) NOT NULL default 'CREATE DEF', `Brand_RowGuid` blob NOT NULL, `Brand_LastContentUpdate` datetime default NULL, PRIMARY KEY (`Brand_ID`), UNIQUE KEY `AK_Brand_Col_2` (`Brand_Description`), KEY `IX_Brand_Col_6` (`Ingredient_ID`), CONSTRAINT `FK_Brand_To_Ingredient_On_Ingredient_ID` FOREIGN KEY (`Ingredient_ID`) REFERENCES `dbo_DRUG_Ingredient` (`Ingredient_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.08 sec) mysql> show create table TopsDataDrug.dbo_DRUG_Ingredient; +---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | dbo_DRUG_Ingredient |CREATE TABLE `dbo_DRUG_Ingredient` ( `Ingredient_ID` varchar(10) NOT NULL default 'CREATE DEF', `Ingredient_UsedAsDrug` tinyint(1) NOT NULL default '0', `Ingredient_Description` varchar(100) NOT NULL default 'CREATE DEFAULT D_EmptyString AS ''''\r\n', `Ingredient_Active` tinyint(1) NOT NULL default '0', `Ingredient_RecordState` smallint(6) NOT NULL default '0', `InternalCategory_ID` varchar(10) NOT NULL default 'CREATE DEF', `DrugStatus_ID` varchar(10) NOT NULL default 'CREATE DEF', `AllergyCode_ID` varchar(10) NOT NULL default 'CREATE DEF', `AllergyCode_ID2` varchar(10) NOT NULL default 'CREATE DEF', `Ingredient_RowGuid` blob NOT NULL, `Ingredient_LastContentUpdate` datetime default NULL, PRIMARY KEY (`Ingredient_ID`), UNIQUE KEY `AK_Ingredient_Col_3` (`Ingredient_Description`), KEY `FK_Ingredient_To_AllergyCode_On_AllergyCode_ID` (`AllergyCode_ID`), KEY `FK_Ingredient_To_AllergyCode_On_AllergyCode_ID2` (`AllergyCode_ID2`), KEY `FK_Ingredient_To_DrugStatus_On_DrugStatus_ID` (`DrugStatus_ID`), KEY `FK_Ingredient_To_InternalCategory_On_InternalCategory_ID` (`InternalCategory_ID`), KEY `Ingredient_Active` (`Ingredient_Active`), CONSTRAINT `FK_Ingredient_To_AllergyCode_On_AllergyCode_ID` FOREIGN KEY (`AllergyCode_ID`) REFERENCES `dbo_DRUG_AllergyCode` (`AllergyCode_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Ingredient_To_AllergyCode_On_AllergyCode_ID2` FOREIGN KEY (`AllergyCode_ID2`) REFERENCES `dbo_DRUG_AllergyCode` (`AllergyCode_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Ingredient_To_DrugStatus_On_DrugStatus_ID` FOREIGN KEY (`DrugStatus_ID`) REFERENCES `dbo_DRUG_DrugStatus` (`DrugStatus_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Ingredient_To_InternalCategory_On_InternalCategory_ID` FOREIGN KEY (`InternalCategory_ID`) REFERENCES `dbo_DRUG_InternalCategory` (`InternalCategory_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.10 sec) mysql> show create table TopsDataDrug.dbo_DNRM_IngredientInteractions; +---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | dbo_DNRM_IngredientInteractions |CREATE TABLE `dbo_DNRM_IngredientInteractions` ( `Interaction_ID` varchar(10) NOT NULL default 'CREATE DEF', `Ingredient_ID1` varchar(10) NOT NULL default 'CREATE DEF', `Ingredient_ID2` varchar(10) NOT NULL default 'CREATE DEF', `IngredientInteraction_Description` text NOT NULL, `IngredientInteractions_RowGuid` blob NOT NULL, PRIMARY KEY (`Interaction_ID`,`Ingredient_ID1`,`Ingredient_ID2`), KEY `IX_IngredientInteractions_Col_2` (`Ingredient_ID1`), KEY `FK_IngredientInteractions_To_Ingredient_On_Ingredient_ID2` (`Ingredient_ID2`), CONSTRAINT `FK_IngredientInteractions_To_Ingredient_On_Ingredient_ID1` FOREIGN KEY (`Ingredient_ID1`) REFERENCES `dbo_DRUG_Ingredient` (`Ingredient_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_IngredientInteractions_To_Ingredient_On_Ingredient_ID2` FOREIGN KEY (`Ingredient_ID2`) REFERENCES `dbo_DRUG_Ingredient` (`Ingredient_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_IngredientInteractions_To_Interaction_On_Interaction_ID` FOREIGN KEY (`Interaction_ID`) REFERENCES `dbo_DRUG_Interaction` (`Interaction_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.52 sec) explain select distinct tt1.Brand_Description,tt2.Brand_Description from TopsDataDrug.dbo_DRUG_Brand tt1,TopsDataDrug.dbo_DRUG_Ingredient tt3,TopsDataDrug.dbo_DRUG_Brand tt2,TopsDataDrug.dbo_DRUG_Ingredient tt4,TopsDataDrug.dbo_DNRM_IngredientInteractions where TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID1=tt1.Ingredient_ID and TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID1=tt3.Ingredient_ID and TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID2=tt2.Ingredient_ID and TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID2=tt4.Ingredient_ID and tt1.Ingredient_ID=tt3.Ingredient_ID and tt2.Ingredient_ID=tt4.Ingredient_ID and tt3.Ingredient_ID <> tt4.Ingredient_ID and tt4.Ingredient_Active=1 and tt3.Ingredient_Active=1 order by tt1.Brand_Description,tt2.Brand_Description limit 3; +----+-------------+---------------------------------+--------+-------------------------------------------------------------------------------------------+---------------------------------+---------+-------------------------------------------------------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------------------+--------+-------------------------------------------------------------------------------------------+---------------------------------+---------+-------------------------------------------------------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | tt3 | ref | PRIMARY,Ingredient_Active | Ingredient_Active | 1 | const | 1137 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | tt1 | ref | IX_Brand_Col_6 | IX_Brand_Col_6 | 30 | TopsDataDrug.tt3.Ingredient_ID | 1 | | | 1 | SIMPLE | dbo_DNRM_IngredientInteractions | ref | IX_IngredientInteractions_Col_2,FK_IngredientInteractions_To_Ingredient_On_Ingredient_ID2 | IX_IngredientInteractions_Col_2 | 30 | TopsDataDrug.tt3.Ingredient_ID | 22 | Using where; Using index | | 1 | SIMPLE | tt4 | eq_ref | PRIMARY,Ingredient_Active | PRIMARY | 30 | TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID2 | 1 | Using where | | 1 | SIMPLE | tt2 | ref | IX_Brand_Col_6 | IX_Brand_Col_6 | 30 | TopsDataDrug.tt4.Ingredient_ID | 2 | Using where | +----+-------------+---------------------------------+--------+-------------------------------------------------------------------------------------------+---------------------------------+---------+-------------------------------------------------------------+------+-----------------------------------------------------------+ select distinct tt1.Brand_Description,tt2.Brand_Description from TopsDataDrug.dbo_DRUG_Brand tt1,TopsDataDrug.dbo_DRUG_Ingredient tt3,TopsDataDrug.dbo_DRUG_Brand tt2,TopsDataDrug.dbo_DRUG_Ingredient tt4,TopsDataDrug.dbo_DNRM_IngredientInteractions where TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID1=tt1.Ingredient_ID and TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID1=tt3.Ingredient_ID and TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID2=tt2.Ingredient_ID and TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID2=tt4.Ingredient_ID and tt1.Ingredient_ID=tt3.Ingredient_ID and tt2.Ingredient_ID=tt4.Ingredient_ID and tt3.Ingredient_ID <> tt4.Ingredient_ID and tt4.Ingredient_Active=1 and tt3.Ingredient_Active=1 order by tt1.Brand_Description,tt2.Brand_Description limit 3; +-------------------+-------------------+ | Brand_Description | Brand_Description | +-------------------+-------------------+ | 8-MOP | Actisite | | 8-MOP | Adoxa | | 8-MOP | Adoxa Pak 1 | +-------------------+-------------------+ 3 rows in set (1 min 45.27 sec) select distinct tt1.Brand_Description,tt2.Brand_Description from TopsDataDrug.dbo_DRUG_Brand tt1,TopsDataDrug.dbo_DRUG_Ingredient tt3,TopsDataDrug.dbo_DRUG_Brand tt2,TopsDataDrug.dbo_DRUG_Ingredient tt4,TopsDataDrug.dbo_DNRM_IngredientInteractions where TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID1=tt1.Ingredient_ID and TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID1=tt3.Ingredient_ID and TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID2=tt2.Ingredient_ID and TopsDataDrug.dbo_DNRM_IngredientInteractions.Ingredient_ID2=tt4.Ingredient_ID and tt1.Ingredient_ID=tt3.Ingredient_ID and tt2.Ingredient_ID=tt4.Ingredient_ID and tt3.Ingredient_ID <> tt4.Ingredient_ID and tt4.Ingredient_Active=1 and tt3.Ingredient_Active=1 order by tt1.Brand_Description,tt2.Brand_Description limit 50; +-------------------+------------------------+ | Brand_Description | Brand_Description | +-------------------+------------------------+ | 8-MOP | Actisite | | 8-MOP | Adoxa | | 8-MOP | Adoxa Pak 1 | | 8-MOP | Adoxa Pak 2 | | 8-MOP | Adsorbocarpine | | 8-MOP | Akarpine | | 8-MOP | Anthra-Derm | | 8-MOP | Anthralin | | 8-MOP | Aquatensen | | 8-MOP | Arestin | | 8-MOP | Azulfidine | | 8-MOP | Azulfidine EN | | 8-MOP | Balnetar | | 8-MOP | Bendroflumethiazide | | 8-MOP | Benzthiazide | | 8-MOP | Chlorothiazide | | 8-MOP | Chlorpromazine HCl | | 8-MOP | Chlorthalidone | | 8-MOP | Coal Tar | | 8-MOP | Compazine | | 8-MOP | Compazine Spansules | | 8-MOP | Compro | | 8-MOP | Declomycin | | 8-MOP | Demeclocycline HCl | | 8-MOP | Diucardin | | 8-MOP | Diurese | | 8-MOP | Diurigen | | 8-MOP | Diuril | | 8-MOP | Diuril Sodium | | 8-MOP | Doryx | | 8-MOP | Doxy 100 | | 8-MOP | Doxy 200 | | 8-MOP | Doxychel Hyclate | | 8-MOP | Doxycycline | | 8-MOP | Doxycycline Hyclate | | 8-MOP | Dritho-Scalp | | 8-MOP | Drithocreme | | 8-MOP | Drithocreme HP | | 8-MOP | Dynacin | | 8-MOP | Enduron | | 8-MOP | Esidrix | | 8-MOP | Exna | | 8-MOP | Ezide | | 8-MOP | Fluphenazine Decanoate | | 8-MOP | Fluphenazine Enanthate | | 8-MOP | Fluphenazine HCl | | 8-MOP | Fulvicin P/G | | 8-MOP | Fulvicin-U/F | | 8-MOP | Gantanol | | 8-MOP | Gantrisin | +-------------------+------------------------+ 50 rows in set (1 min 46.59 sec)