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:
my query
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
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