Performance

På forhånd undskyld de store søgninger!

De er næsten ens, men performance-mæssigt er der utrolig stor foreskel.
Mit spørgsmål er bare: Hvorfor denne store forskel?


Søgning på et medarbejdernavn (tager 0,3 sek.)
----------------------------------------------
SELECT e.employeeid,
IF(ex.beocademyno Is Not Null, ex.jobtitle, e.jobtitle) jobtitle,
IF(ex.beocademyno Is Not Null, ex.firstname, e.firstname) firstname,
IF(ex.beocademyno Is Not Null, ex.lastname, e.lastname) lastname,
IF(ex.beocademyno Is Not Null, ex.address1, e.address1) address1,
IF(ex.beocademyno Is Not Null, ex.address2, e.address2) address2,
IF(ex.beocademyno Is Not Null, ex.zipcode, e.zipcode) zipcode,
IF(ex.beocademyno Is Not Null, ex.city, e.city) city,
IF(ex.beocademyno Is Not Null, ex.countryid, e.countryid) countryid,
IF(ex.beocademyno Is Not Null, ex.phone, e.phone) phone,
IF(ex.beocademyno Is Not Null, ex.mobile, e.mobile) mobile,
IF(ex.beocademyno Is Not Null, ex.fax, e.fax) fax,
IF(ex.beocademyno Is Not Null, ex.email, e.email) email,
IF(ex.beocademyno Is Not Null, ex.birthday, e.birthday) birthday, e.hiredsince,
IF(ex.beocademyno Is Not Null, ex.sex, e.sex) sex,
IF(ex.beocademyno Is Not Null, ex.nativelanguage, e.nativelanguage) nativelanguage,
IF(ex.beocademyno Is Not Null, ex.otherlanguage, e.otherlanguage) otherlanguage,
IF(ex.beocademyno Is Not Null, ex.region, e.region) region, e.careeraspiration, e.cv, e.employeeno, e.beocademyno,
IF(ex.beocademyno Is Not Null, ex.cardno, e.cardno) cardno,
IF(ex.beocademyno Is Not Null, ex.cardstatus, e.cardstatus) cardstatus,
IF(ex.beocademyno Is Not Null, ex.active, e.active) active, e.salesperson, e.technician, e.skillsinaudio, e.skillsinvideo, e.skillsininstallation, e.trainer, e.shopowner, e.leader, e.internalemployee, e.username, e.password, e.access, e.externshopno, e.shopid, ExternShop.name
FROM Employee e
LEFT JOIN ExternEmployee ex ON e.beocademyno=ex.beocademyno
LEFT JOIN Shop ON e.externshopno = Shop.externshopno
LEFT JOIN ExternShop ON ExternShop.externshopno = Shop.externshopno
WHERE ((STRCMP(SOUNDEX(e.firstname), SOUNDEX('Hans')) = 0 OR e.firstname LIKE '%Hans%')
OR (STRCMP(SOUNDEX(ex.firstname), SOUNDEX('Hans')) = 0 OR ex.firstname LIKE '%Hans%'))
ORDER BY e.firstname, e.lastname


Søgning på et butiksnavn (tager 9,6 sek.)
-----------------------------------------
SELECT e.employeeid,
IF(ex.beocademyno Is Not Null, ex.jobtitle, e.jobtitle) jobtitle,
IF(ex.beocademyno Is Not Null, ex.firstname, e.firstname) firstname,
IF(ex.beocademyno Is Not Null, ex.lastname, e.lastname) lastname,
IF(ex.beocademyno Is Not Null, ex.address1, e.address1) address1,
IF(ex.beocademyno Is Not Null, ex.address2, e.address2) address2,
IF(ex.beocademyno Is Not Null, ex.zipcode, e.zipcode) zipcode,
IF(ex.beocademyno Is Not Null, ex.city, e.city) city,
IF(ex.beocademyno Is Not Null, ex.countryid, e.countryid) countryid,
IF(ex.beocademyno Is Not Null, ex.phone, e.phone) phone,
IF(ex.beocademyno Is Not Null, ex.mobile, e.mobile) mobile,
IF(ex.beocademyno Is Not Null, ex.fax, e.fax) fax,
IF(ex.beocademyno Is Not Null, ex.email, e.email) email,
IF(ex.beocademyno Is Not Null, ex.birthday, e.birthday) birthday, e.hiredsince,
IF(ex.beocademyno Is Not Null, ex.sex, e.sex) sex,
IF(ex.beocademyno Is Not Null, ex.nativelanguage, e.nativelanguage) nativelanguage,
IF(ex.beocademyno Is Not Null, ex.otherlanguage, e.otherlanguage) otherlanguage,
IF(ex.beocademyno Is Not Null, ex.region, e.region) region, e.careeraspiration, e.cv, e.employeeno, e.beocademyno, IF(ex.beocademyno Is Not Null, ex.cardno, e.cardno) cardno,
IF(ex.beocademyno Is Not Null, ex.cardstatus, e.cardstatus) cardstatus,
IF(ex.beocademyno Is Not Null, ex.active, e.active) active, e.salesperson, e.technician, e.skillsinaudio, e.skillsinvideo, e.skillsininstallation, e.trainer, e.shopowner, e.leader, e.internalemployee, e.username, e.password, e.access, e.externshopno, e.shopid, ExternShop.name
FROM Employee e
LEFT JOIN ExternEmployee ex ON e.beocademyno=ex.beocademyno
LEFT JOIN Shop ON e.externshopno = Shop.externshopno
LEFT JOIN ExternShop ON ExternShop.externshopno = Shop.externshopno
WHERE (STRCMP(SOUNDEX(ExternShop.name), SOUNDEX('fona')) = 0 OR ExternShop.name LIKE '%fona%')
ORDER BY e.firstname, e.lastname


Søgning på et medarbejdernavn og butiksnavn (tager 0,2 sek.)
------------------------------------------------------------
SELECT e.employeeid,
IF(ex.beocademyno Is Not Null, ex.jobtitle, e.jobtitle) jobtitle,
IF(ex.beocademyno Is Not Null, ex.firstname, e.firstname) firstname,
IF(ex.beocademyno Is Not Null, ex.lastname, e.lastname) lastname,
IF(ex.beocademyno Is Not Null, ex.address1, e.address1) address1,
IF(ex.beocademyno Is Not Null, ex.address2, e.address2) address2,
IF(ex.beocademyno Is Not Null, ex.zipcode, e.zipcode) zipcode,
IF(ex.beocademyno Is Not Null, ex.city, e.city) city,
IF(ex.beocademyno Is Not Null, ex.countryid, e.countryid) countryid,
IF(ex.beocademyno Is Not Null, ex.phone, e.phone) phone,
IF(ex.beocademyno Is Not Null, ex.mobile, e.mobile) mobile,
IF(ex.beocademyno Is Not Null, ex.fax, e.fax) fax,
IF(ex.beocademyno Is Not Null, ex.email, e.email) email,
IF(ex.beocademyno Is Not Null, ex.birthday, e.birthday) birthday, e.hiredsince,
IF(ex.beocademyno Is Not Null, ex.sex, e.sex) sex,
IF(ex.beocademyno Is Not Null, ex.nativelanguage, e.nativelanguage) nativelanguage,
IF(ex.beocademyno Is Not Null, ex.otherlanguage, e.otherlanguage) otherlanguage,
IF(ex.beocademyno Is Not Null, ex.region, e.region) region, e.careeraspiration, e.cv, e.employeeno, e.beocademyno,
IF(ex.beocademyno Is Not Null, ex.cardno, e.cardno) cardno,
IF(ex.beocademyno Is Not Null, ex.cardstatus, e.cardstatus) cardstatus,
IF(ex.beocademyno Is Not Null, ex.active, e.active) active, e.salesperson, e.technician, e.skillsinaudio, e.skillsinvideo, e.skillsininstallation, e.trainer, e.shopowner, e.leader, e.internalemployee, e.username, e.password, e.access, e.externshopno, e.shopid, ExternShop.name
FROM Employee e
LEFT JOIN ExternEmployee ex ON e.beocademyno=ex.beocademyno
LEFT JOIN Shop ON e.externshopno = Shop.externshopno
LEFT JOIN ExternShop ON ExternShop.externshopno = Shop.externshopno
WHERE ((STRCMP(SOUNDEX(e.firstname), SOUNDEX('Hans')) = 0 OR e.firstname LIKE '%Hans%')
OR (STRCMP(SOUNDEX(ex.firstname), SOUNDEX('Hans')) = 0 OR ex.firstname LIKE '%Hans%'))
AND (STRCMP(SOUNDEX(ExternShop.name), SOUNDEX('Fona')) = 0 OR ExternShop.name LIKE '%Fona%')
ORDER BY e.firstname, e.lastname



Tabellerne:
-----------
Employee:
---------
employeeid, jobtitle, firstname, lastname, address1, address2, zipcode, city, countryid, phone, mobile, fax, email, birthday, hiredsince, sex, nativelanguage, otherlanguage, region, careeraspiration, cv, employeeno, beocademyno, cardno, cardstatus, active, salesperson, technician, skillsinaudio, skillsinvideo, skillsininstallation, trainer, shopowner, leader, internalemployee, username, password, access, shopid

employeeid er primærnøgle
countryid er fremmednølge
beocademyno er fremmednølge
shopid er fremmednølge


ExternEmployee:
---------------
beocademyno, jobtitle, firstname, lastname, address1, address2, zipcode, city, country, state, cardno, cardstatus, active, phone, mobile, fax, email, birthday, sex, nativelanguage, otherlanguage, region

beocademyno er primærnøgle


Shop:
-----
shopid, shoptypeid, regionid, externshopno

shopid er primærnøgle
resten er fremmednøgler


ExternShop:
-----------
externshopno, name, address1, address2, zipcode, city, country, state, phone, mobile, fax, email, segment, region, type

externshopno er primærnøgle