Simple performance Question

[Yasin Hinislioglu]

Take a look at following scenarios,

Scenario A :
1) SELECT * FROM urun WHERE urun.UrunKategoriID = '3';
2) SELECT * FROM urun WHERE urun.UrunKategoriID = '2';

Query 1 returns 210.006 rows and takes 183,8238 seconds query time.
Query 2 returns 3 rows and takes 0.05 seconds query time.

Scenario B :
1) SELECT * FROM urun USE INDEX(ind_urun_urunadi) WHERE urun.UrunKategoriID = '3' ORDER BY urunadi
2) SELECT * FROM urun USE INDEX(ind_urun_urunadi) WHERE urun.UrunKategoriID = '2' ORDER BY urunadi

Query 1 returns 210.006 rows and takes 0.2796 seconds query time.
Query 2 returns 3 rows and takes 16.614 seconds query time.

Questions :
1) Why does statement Query 2 in (Scenario B) takes more time than Query 2 in (Scenario A)?

2) Query 1 returns more rows than Query 2 in both scenarios. But Query 1 works much more efficient in Scenario B. Why?

3) What improvement(s) should i make to have less query time when i use Order By clause?


SQL Explains :
Scenario A Query 1
id,select_type,table,type,possible_keys,key,key_len,ref,rows,extra
1, 'SIMPLE', 'urun', 'ref', 'FK_URUN_URUNKATEGORIID', 'FK_URUN_URUNKATEGORIID', '4', 'const', 106675, ''

Scenario A Query 2
1, 'SIMPLE', 'urun', 'ref', 'FK_URUN_URUNKATEGORIID', 'FK_URUN_URUNKATEGORIID', '4', 'const', 3, ''

Scenario B Query 1
1, 'SIMPLE', 'urun', 'index', '', 'IND_URUN_URUNADI', '102', '', 213351, 'Using where'

Scenario B Query 2
1, 'SIMPLE', 'urun', 'index', '', 'IND_URUN_URUNADI', '102', '', 213351, 'Using where'

Tables Create Statements:
'CREATE TABLE `urun` (
`UrunID` int(10) unsigned NOT NULL auto_increment,
`UrunAdi` varchar(100) NOT NULL default '',
`Aciklama` varchar(100) NOT NULL default '',
`KDV` int(10) unsigned NOT NULL default '0',
`UrunKategoriID` int(10) unsigned NOT NULL default '0',
`BirimID` int(10) unsigned NOT NULL default '0',
`BarkodNo` varchar(100) NOT NULL default '',
`SubeID` int(10) unsigned NOT NULL default '0',
`IslemID` int(10) unsigned default '0',
PRIMARY KEY (`UrunID`),
UNIQUE KEY `IND_URUN_BARKODNO` USING BTREE (`BarkodNo`),
KEY `FK_URUN_URUNKATEGORIID` (`UrunKategoriID`),
KEY `FK_URUN_BIRIMID` (`BirimID`),
KEY `IND_URUN_URUNADI` (`UrunAdi`),
KEY `IND_URUN_KDV` (`KDV`),
KEY `FK_URUN_SUBEID` (`SubeID`),
KEY `FK_URUN_ISLEMID` (`IslemID`),
CONSTRAINT `FK_URUN_BIRIMID` FOREIGN KEY (`BirimID`) REFERENCES `birim` (`BirimID`),
CONSTRAINT `FK_URUN_ISLEMID` FOREIGN KEY (`IslemID`) REFERENCES `islem` (`IslemID`) ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT `FK_URUN_SUBEID` FOREIGN KEY (`SubeID`) REFERENCES `sube` (`SubeID`),
CONSTRAINT `FK_URUN_URUNKATEGORIID` FOREIGN KEY (`UrunKategoriID`) REFERENCES `urunkategori` (`UrunKategoriID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin5;

'urunkategori', 'CREATE TABLE `urunkategori` (
`UrunKategoriID` int(10) unsigned NOT NULL auto_increment,
`KategoriAdi` varchar(100) NOT NULL default '',
`SubeID` int(10) unsigned NOT NULL default '0',
`IslemID` int(10) unsigned default '0',
PRIMARY KEY (`UrunKategoriID`),
KEY `IND_URUNKATEGORI_KATEGORIADI` (`KategoriAdi`),
KEY `FK_URUNKATEGORI_SUBEID` (`SubeID`),
KEY `FK_URUNKATEGORI_ISLEMID` (`IslemID`),
CONSTRAINT `FK_URUNKATEGORI_ISLEMID` FOREIGN KEY (`IslemID`) REFERENCES `islem` (`IslemID`) ON DELETE SET NULL ON UPDATE SET NULL,
CONSTRAINT `FK_URUNKATEGORI_SUBEID` FOREIGN KEY (`SubeID`) REFERENCES `sube` (`SubeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin5;