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

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


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