Possible Faster Query

I have this query on my current application:

SELECT count(sql_id) AS total, DATE_FORMAT(insertdate, '%H:%i' ) AS
mydate FROM momt WHERE 1=1 AND insertdate BETWEEN DATE_ADD(NOW(),
INTERVAL - 1 HOUR) AND NOW() AND (sms_type = 1 OR sms_type =2) GROUP BY
mydate ORDER BY insertdate

Is there any possibilities to speed up the query? Because my data become
very huge and this query seems getting slower. TIA.



Willy


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Possible Faster Query

--Apple-Mail-36-471312207
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

On 10 Jul 2009, at 03:44, sangprabv wrote:

> SELECT count(sql_id) AS total, DATE_FORMAT(insertdate, '%H:%i' ) AS
> mydate FROM momt WHERE 1=1 AND insertdate BETWEEN DATE_ADD(NOW(),
> INTERVAL - 1 HOUR) AND NOW() AND (sms_type = 1 OR sms_type =2) GROUP
> BY
> mydate ORDER BY insertdate


Firstly do an EXPLAIN on that and make sure it's got and is using
appropriate indexes. Straight off a couple of small changes should help:

SELECT count(*) AS total, DATE_FORMAT(insertdate, '%H:%i' ) AS
mydate FROM momt WHERE 1=1 AND insertdate > DATE_ADD(NOW(),
INTERVAL - 1 HOUR) AND (sms_type = 1 OR sms_type =2) GROUP BY
mydate ORDER BY insertdate

This assumes that insertdate can't be in the future...

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@synchromedia.co.uk | http://www.synchromedia.co.uk/



--Apple-Mail-36-471312207
Content-Disposition: attachment;
filename=smime.p7s
Content-Type: application/pkcs7-signature;
name=smime.p7s
Content-Transfer-Encoding: base64

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIILHzCCBN0w
ggPFoAMCAQICEHGS++YZX6xNEoV0cTSiGKcwDQYJKoZIhvcNAQEFBQAwezEL MAkGA1UEBhMCR0Ix
GzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwHU2FsZm9y ZDEaMBgGA1UECgwR
Q29tb2RvIENBIExpbWl0ZWQxITAfBgNVBAMMGEFBQSBDZXJ0aWZpY2F0ZSBT ZXJ2aWNlczAeFw0w
NDAxMDEwMDAwMDBaFw0yODEyMzEyMzU5NTlaMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMCVVQx
FzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRS VVNUIE5ldHdvcmsx
ITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMt VVROLVVTRVJGaXJz
dC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG 9w0BAQEFAAOCAQ8A
MIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVNNRm5 pELlzkniii8efNIx
B8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQylbsMTzC9 mKALi+VuG6JG+ni8
om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXqvgvOdjp6Dpvq /NonWz1zHyLmSGHG
TPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6hw2v+vPhwvCkxWeM 1tZUOt4KpLoDd7Nl
yP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu9mIwFIws6wIDAQABo4IB JzCCASMwHwYDVR0j
BBgwFoAUoBEKIz6W8Qfs4q8p74Klf9AwpLQwHQYDVR0OBBYEFImCZ33EnSZw AEu0UEh83j2uBG59
MA4GA1UdDwEB/wQEAwIBBjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdJQQWMBQG CCsGAQUFBwMCBggr
BgEFBQcDBDARBgNVHSAECjAIMAYGBFUdIAAwewYDVR0fBHQwcjA4oDagNIYy aHR0cDovL2NybC5j
b21vZG9jYS5jb20vQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNqA0oDKG MGh0dHA6Ly9jcmwu
Y29tb2RvLm5ldC9BQUFDZXJ0aWZpY2F0ZVNlcnZpY2VzLmNybDARBglghkgB hvhCAQEEBAMCAQYw
DQYJKoZIhvcNAQEFBQADggEBAJ2Vyzy4fqUJxB6/C8LHdo45PJTGEKpPDMng q4RdiVTgZTvzbRx8
NywlVF+WIfw3hJGdFdwUT4HPVB1rbEVgxy35l1FM+WbKPKCCjKbI8OLp1Er5 7D9Wyd12jMOCAU9s
APMeGmF0BEcDqcZAV5G8ZSLFJ2dPV9tkWtmNH7qGL/QGrpxp7en0zykX2OBK nxogL5dMUbtGB8SK
N04g4wkxaMeexIud6H4RvDJoEJYRmETYKlFgTYjrdDrfQwYyyDlWjDoRUtNB pEMD9O3vMyfbOeAU
TibJ2PU54om4k123KSZB6rObroP8d3XK6Mq1/uJlSmM+RMTQw16Hc6mYHK9/ FX8wggY6MIIFIqAD
AgECAhBx3xrKwDO5B7CQiO3prEe8MA0GCSqGSIb3DQEBBQUAMIGuMQswCQYD VQQGEwJVUzELMAkG
A1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVU aGUgVVNFUlRSVVNU
IE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2 MDQGA1UEAxMtVVRO
LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMB4X DTA4MTEyODAwMDAw
MFoXDTA5MTEyODIzNTk1OVowgeMxNTAzBgNVBAsTLENvbW9kbyBUcnVzdCBO ZXR3b3JrIC0gUEVS
U09OQSBOT1QgVkFMSURBVEVEMUYwRAYDVQQLEz1UZXJtcyBhbmQgQ29uZGl0 aW9ucyBvZiB1c2U6
IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0b3J5MR8wHQYDVQQLExYo YykyMDAzIENvbW9k
byBMaW1pdGVkMRcwFQYDVQQDEw5NYXJjdXMgQm9pbnRvbjEoMCYGCSqGSIb3 DQEJARYZbWFyY3Vz
QHN5bmNocm9tZWRpYS5jby51azCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC AQoCggEBAJq12Fwn
mDc8j/J+fsyJFdXkxSsbex2n5eXKm0Tjwy+IPE2F5jxpaITqju9WtxMcsjtt P+CZfAdOQQD/R1fg
jPQLXDifxudsMogyOmX2HdoEpGFITEnes21U+hKTU87DlIM+hEZViuOqS8Ym Xj8oNISFYAa90lvs
nt7jtu/eyDdvrZJ0q6QnntRUaeSsz8ngV8Lqshmo/1qmz67JL77MfJgkSfEP 6fJkqb/e9kKjJ1Tn
HipP2PmZTQUnHwcOs/0uaSgOumgpgMyX8fiQ33VV/DGMcTl9P0oLB0GopAKw qlHoXPCJXFkX6LLi
Mm9bdoKUIW0kwnRx1HWjvDrih9UeMmECAwEAAaOCAhswggIXMB8GA1UdIwQY MBaAFImCZ33EnSZw
AEu0UEh83j2uBG59MB0GA1UdDgQWBBSiqul8UOEn/VVzRp+EXywCAx973TAO BgNVHQ8BAf8EBAMC
BaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYLKwYBBAGy MQEDBQIwEQYJYIZI
AYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQEBMCswKQYI KwYBBQUHAgEWHWh0
dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNVHR8EgZ0wgZowTKBK oEiGRmh0dHA6Ly9j
cmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3QtQ2xpZW50QXV0aGVudGlj YXRpb25hbmRFbWFp
bC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29tb2RvLm5ldC9VVE4tVVNFUkZp cnN0LUNsaWVudEF1
dGhlbnRpY2F0aW9uYW5kRW1haWwuY3JsMGwGCCsGAQUFBwEBBGAwXjA2Bggr BgEFBQcwAoYqaHR0
cDovL2NydC5jb21vZG9jYS5jb20vVVROQUFBQ2xpZW50Q0EuY3J0MCQGCCsG AQUFBzABhhhodHRw
Oi8vb2NzcC5jb21vZG9jYS5jb20wJAYDVR0RBB0wG4EZbWFyY3VzQHN5bmNo cm9tZWRpYS5jby51
azANBgkqhkiG9w0BAQUFAAOCAQEAKfnMTtYONyd2o63aCpSJs8GpopYaEU3k sEBgOAfTFe52yx+k
6eLyah2I0N+lnBmDdLDeR7Eel6zWjKZmaNW9r1h7lRkuzLt6VqpjbBPefOk3 UWkjwJ8j0opKrFSW
UXg+fghnuNVptw3STOyWQKk+fEKtiuf+Ygpnl+QnJWsVjSeasklYqLImXYmn IM3N4y/18UQFztXD
+Z+cn7ZLnBXEfSb/GatxP9JYuhLRTV1YMwtcVhGydHvJTzug+5+wBPdlI8L1 5YecgOR4JxoNJ5T9
Odt0TMPxxVi+m2pZw6Ni3fh8yeKoLsBreKTAX3IsKGsIPJzczQvnVeRrTLaH is9r2DGCA/wwggP4
AgEBMIHDMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcT DlNhbHQgTGFrZSBD
aXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsT GGh0dHA6Ly93d3cu
dXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQg QXV0aGVudGljYXRp
b24gYW5kIEVtYWlsAhBx3xrKwDO5B7CQiO3prEe8MAkGBSsOAwIaBQCgggIN MBgGCSqGSIb3DQEJ
AzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTA5MDcxMDA4MTkyOFow IwYJKoZIhvcNAQkE
MRYEFJvKudDOFRH9OB0RECxs4aEfTFHRMIHUBgkrBgEEAYI3EAQxgcYwgcMw ga4xCzAJBgNVBAYT
AlVTMQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAc BgNVBAoTFVRoZSBV
U0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1 c3QuY29tMTYwNAYD
VQQDEy1VVE4tVVNFUkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQg RW1haWwCEHHfGsrA
M7kHsJCI7emsR7wwgdYGCyqGSIb3DQEJEAILMYHGoIHDMIGuMQswCQYDVQQG EwJVUzELMAkGA1UE
CBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUg VVNFUlRSVVNUIE5l
dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVROLVVT
RVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhBx3xrK wDO5B7CQiO3prEe8
MA0GCSqGSIb3DQEBAQUABIIBAB6bwrg/9TzgxCy1whLY8W7DvN1rnTAPwXIM Tm0xxaY9iDXBuSwT
jhB7agNgUgJ844028uSSBVAV9b7w/GznfHXRuqYtJOtgOs24uWMrWns4KTvV OnWq302Kx6J9kGk3
TGQuZkszTIVx+/fh47B58g99ix8LMBgwP8QggFuOZzt/P1YHbHoIVW5CEIMe kv/iUuSVQ3PdpGp5
DgdhE2zpycL3TM0/3736mv9gwy7X7gPIXmzhwZnLbJoIqdP6DVbwWJFOe7vM RSXay2teOW+vT6+S
FsIBfLKkegtzDiggBRBfHtsO+b6v1ojIGyvnaeKHhlbbq2MQqq9Z8ZTmzAYk sRAAAAAAAAA=

--Apple-Mail-36-471312207--