SQL: hana maikaʻi ʻole me ka papa kuhikuhi

ʻAkahi nō au a alo me kēia pilikia hana o ka waihona database SQL: Ma ka papa ʻaina SQL i kapa ʻia ʻo "nā mea hoʻohana" me ~ 1000,000 mea komo, ua lawe ʻia he 28 kekona i ʻae ʻole ʻia e hoʻoholo ai i ka helu o nā hoʻopaʻa inoa i kēia lā. Ma ke kolamu "hana", ua mālama ʻia nā manawa o nā hoʻopaʻa inoa ʻana i ka mana "Ymd H: i: s".


Ua hoʻohana ʻia ka nīnau

SELECT COUNT(ID) FROM users WHERE DATE(created) = '2014-12-21';

i alakaʻi ʻia i kēia manawa hoʻouka, ʻoiai ua loaʻa mua kahi papa kuhikuhi ma ka kolamu "hana" o ka mana DATETIME

ALTER TABLE `users` ADD INDEX `created` (`created`);

ua hoʻonohonoho ʻia. ʻO ke kumu o ka hana maikaʻi ʻole ke kāhea hana o DATE: ʻO ke kolamu i kuhikuhi ʻia "hana ʻia" i hoʻouna ʻia e ka hana DATE a hana ʻole i ka papa kuhikuhi. ʻIke wale ka Query Optimizer "i haku ʻia", akā ʻaʻole ka hopena o DATE (hana ʻia). ʻO ka nīnau i hoʻoponopono ʻia

SELECT COUNT(ID) FROM users WHERE created BETWEEN '2014-12-21' AND '2014-12-21 23:59:59';

hiki i ka hopena i makemake ʻia i ka manawa o 0.4 kekona.

Hope