Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

optimization inner join

Status
Not open for further replies.

nego78

Programmer
Jun 13, 2004
129
0
0
PL
hello :)

I've query which use 2 inner joins
i've thought that i use proper indexes but EXPLAIN showed that i'm wrong

my tables:
Code:
CREATE TABLE `telefony_Cechy` (
  `Cechy_Id` int(10) unsigned NOT NULL auto_increment,
  `Cechy_Nazwa` varchar(255) collate utf8_polish_ci NOT NULL default '',
  `Cechy_Kolejnosc` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Cechy_Id`),
  UNIQUE KEY `IX_Nazwa` USING BTREE (`Cechy_Nazwa`),
  KEY `IX_Kolejnosc` (`Cechy_Kolejnosc`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

-- --------------------------------------------------------

CREATE TABLE `telefony_CechyDoProduktow` (
  `Produkty_Id` int(10) unsigned NOT NULL default '0',
  `Cechy_Id` int(10) unsigned NOT NULL default '0',
  `CechyWartosci_Id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Produkty_Id`,`Cechy_Id`,`CechyWartosci_Id`),
  KEY `IX_Cechy` USING BTREE (`Cechy_Id`),
  KEY `IX_CechyWartosci` USING BTREE (`CechyWartosci_Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

-- --------------------------------------------------------

CREATE TABLE `telefony_CechyWartosci` (
  `CechyWartosci_Id` int(10) unsigned NOT NULL auto_increment,
  `CechyWartosci_Nazwa` varchar(255) collate utf8_polish_ci NOT NULL default '',
  PRIMARY KEY  (`CechyWartosci_Id`),
  UNIQUE KEY `IX_Nazwa` USING BTREE (`CechyWartosci_Nazwa`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

my query
Code:
SELECT b.Cechy_Nazwa,c.CechyWartosci_Nazwa FROM telefony_CechyDoProduktow AS a INNER JOIN telefony_Cechy AS b ON a.Cechy_Id=b.Cechy_Id INNER JOIN telefony_CechyWartosci AS c ON a.CechyWartosci_Id=c.CechyWartosci_Id WHERE a.Produkty_Id = 480 ORDER BY b.Cechy_Kolejnosc;

when i change inner joins into left joins table b is using index
also order by is not optimized but i can throw it away

if you have any suggestion how to repair this query (or tables) i'll be grateful

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top