Query optimieren.

Diese Seite verwendet Cookies. Durch die Nutzung unserer Seite erklären Sie sich damit einverstanden, dass wir Cookies setzen. Weitere Informationen

  • Query optimieren.

    Vorab: Die Werte usw sind frei erfunden. Dient nur zur veranschaulichung meiner Frage.^^

    Also, ich habe folgende Tabelle:
    [table='ID,browser,timestamp']
    [*]1
    [*]Opera
    [*]1335823200

    [*]2
    [*]Firefox
    [*]1335909599

    [*]3
    [*]Opera
    [*]1335995999

    [*]4
    [*]Chrome
    [*]1335909600
    [/table]

    Und nun möchte ich die gesamt anzahl an einträgen zwischen zwei zeitpunkten (timestamp) herausfinden in der unter "browser" ein bestimmter Browser eingetragen ist.

    Das ganze eben für 6 browser, und 12 zeitpunkte (12 Monate). Zusammengefasst will ich also wissen, wie oft die werte in einem monat vorkommen. Dafür hab ich bis jetzt diese Query zusammengestellt:

    SQL-Abfrage

    1. SELECT
    2. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1335823200" AND "1335909599")) AS `1_ie`,
    3. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1335823200" AND "1335909599")) AS `1_moz`,
    4. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1335823200" AND "1335909599")) AS `1_goo`,
    5. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1335823200" AND "1335909599")) AS `1_saf`,
    6. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1335823200" AND "1335909599")) AS `1_ope`,
    7. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1335823200" AND "1335909599")) AS `1_net`,
    9. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1335909600" AND "1335995999")) AS `2_ie`,
    10. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1335909600" AND "1335995999")) AS `2_moz`,
    11. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1335909600" AND "1335995999")) AS `2_goo`,
    12. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1335909600" AND "1335995999")) AS `2_saf`,
    13. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1335909600" AND "1335995999")) AS `2_ope`,
    14. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1335909600" AND "1335995999")) AS `2_net`,
    16. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1335996000" AND "1336082399")) AS `3_ie`,
    17. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1335996000" AND "1336082399")) AS `3_moz`,
    18. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1335996000" AND "1336082399")) AS `3_goo`,
    19. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1335996000" AND "1336082399")) AS `3_saf`,
    20. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1335996000" AND "1336082399")) AS `3_ope`,
    21. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1335996000" AND "1336082399")) AS `3_net`,
    23. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1336082400" AND "1336168799")) AS `4_ie`,
    24. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1336082400" AND "1336168799")) AS `4_moz`,
    25. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1336082400" AND "1336168799")) AS `4_goo`,
    26. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1336082400" AND "1336168799")) AS `4_saf`,
    27. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1336082400" AND "1336168799")) AS `4_ope`,
    28. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1336082400" AND "1336168799")) AS `4_net`,
    30. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1336168800" AND "1336255199")) AS `5_ie`,
    31. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1336168800" AND "1336255199")) AS `5_moz`,
    32. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1336168800" AND "1336255199")) AS `5_goo`,
    33. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1336168800" AND "1336255199")) AS `5_saf`,
    34. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1336168800" AND "1336255199")) AS `5_ope`,
    35. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1336168800" AND "1336255199")) AS `5_net`,
    37. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1336255200" AND "1336341599")) AS `6_ie`,
    38. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1336255200" AND "1336341599")) AS `6_moz`,
    39. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1336255200" AND "1336341599")) AS `6_goo`,
    40. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1336255200" AND "1336341599")) AS `6_saf`,
    41. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1336255200" AND "1336341599")) AS `6_ope`,
    42. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1336255200" AND "1336341599")) AS `6_net`,
    44. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1336341600" AND "1336427999")) AS `7_ie`,
    45. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1336341600" AND "1336427999")) AS `7_moz`,
    46. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1336341600" AND "1336427999")) AS `7_goo`,
    47. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1336341600" AND "1336427999")) AS `7_saf`,
    48. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1336341600" AND "1336427999")) AS `7_ope`,
    49. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1336341600" AND "1336427999")) AS `7_net`,
    51. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1336428000" AND "1336514399")) AS `8_ie`,
    52. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1336428000" AND "1336514399")) AS `8_moz`,
    53. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1336428000" AND "1336514399")) AS `8_goo`,
    54. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1336428000" AND "1336514399")) AS `8_saf`,
    55. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1336428000" AND "1336514399")) AS `8_ope`,
    56. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1336428000" AND "1336514399")) AS `8_net`,
    58. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1336514400" AND "1336600799")) AS `9_ie`,
    59. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1336514400" AND "1336600799")) AS `9_moz`,
    60. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1336514400" AND "1336600799")) AS `9_goo`,
    61. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1336514400" AND "1336600799")) AS `9_saf`,
    62. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1336514400" AND "1336600799")) AS `9_ope`,
    63. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1336514400" AND "1336600799")) AS `9_net`,
    65. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1336600800" AND "1336687199")) AS `10_ie`,
    66. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1336600800" AND "1336687199")) AS `10_moz`,
    67. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1336600800" AND "1336687199")) AS `10_goo`,
    68. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1336600800" AND "1336687199")) AS `10_saf`,
    69. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1336600800" AND "1336687199")) AS `10_ope`,
    70. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1336600800" AND "1336687199")) AS `10_net`,
    72. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1336687200" AND "1336773599")) AS `11_ie`,
    73. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1336687200" AND "1336773599")) AS `11_moz`,
    74. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1336687200" AND "1336773599")) AS `11_goo`,
    75. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1336687200" AND "1336773599")) AS `11_saf`,
    76. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1336687200" AND "1336773599")) AS `11_ope`,
    77. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1336687200" AND "1336773599")) AS `11_net`,
    79. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Internet Explorer' AND (`timestamp` BETWEEN "1336773600" AND "1336859999")) AS `12_ie`,
    80. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Mozilla Firefox' AND (`timestamp` BETWEEN "1336773600" AND "1336859999")) AS `12_moz`,
    81. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Google Chrome' AND (`timestamp` BETWEEN "1336773600" AND "1336859999")) AS `12_goo`,
    82. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Apple Safari' AND (`timestamp` BETWEEN "1336773600" AND "1336859999")) AS `12_saf`,
    83. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Opera' AND (`timestamp` BETWEEN "1336773600" AND "1336859999")) AS `12_ope`,
    84. (SELECT COUNT(*) FROM `tabelle` WHERE `browser` = 'Netscape' AND (`timestamp` BETWEEN "1336773600" AND "1336859999")) AS `12_net`
    Alles anzeigen


    Das liefert mir dann ein array mit 76 Daten zurück, welches ich entsprechend weiter verarbeite. Die query funktioniert einwandfrei, jedoch stellt sich mir die Frage ob es vielleicht noch eine besser methode gibt das ganze abzufragen, ohne 76 querys draus zu machen^^. Zudem muss ich noch anmerken, das die Tabelle relativ zugüg wächst, und ~ 1Mio einträge vorhanden sind. Dementsprechend sollte das ganze auch nicht zu lange dauern.

    Ich freu mich schonmal auf Antworten und/oder Tipps^^

    Mfg.