Optymalizacja zapytania Opcje

[matino]

Mam tabelę, w której w każdym rzędzie przechowuję m.in. datę utworzenia oraz login.
Jako rezultat ma wyjść tabelka - kolumny loginy, wiersze daty a w środku liczba wystąpień w tabeli dla danej daty i danego loginu:

data | login1 | login2 | suma
2011-06-18 6 7 13
2011-06-19 2 1 3
Razem: 8 8 16

Na tą chwilę zrobiłem to w sposób taki, że pobieram jednym selectem distinct daty, drugim selectem loginy.
Następnie lecę podwójnym foreach i dla każdej daty, wykonuję select count(*) dla każdego loginu.
Takie rozwiązanie jest jednak mało wydajne (przy 12 datach i 9 loginach łączny czas zapytania zajmuje 2 sekundy ).

Może ma ktoś wydajniejszy sposób do przetestowania?

Poniżej kod jakby kogoś interesował:

[PHP] pobierz, plaintext 
public static function get_cases_by_users($users, $start = NULL, $end = NULL)
{
        if (!is_array($users))
        {
            return NULL;
        }
        if (empty($start))
        {
            $start = 0;
        }
        if (empty($end))
        {
            $end = 9999999999;
        }
 
        // Get dates first where cases were generated
        $dates = DB::select(array('FROM_UNIXTIME("create_date", \'%Y-%m-%d\')', 'create_date'))
                        ->distinct('create_date')
                        ->from(table')
                        ->where('create_date', 'BETWEEN', array($start, $end + Date::DAY))
                        ->order_by('create_date')
                        ->execute()->as_array('create_date');
 
        // Count users for calculated dates
        $result = array();
        foreach ($dates as $date => $value)
        {
            $result[$date] = array();
            foreach ($users as $user)
            {
                $count = DB::select(array('COUNT("username")', 'total'))
                                ->from('table')
                                ->where(array('FROM_UNIXTIME("create_date", \'%Y-%m-%d\')', NULL), 'LIKE', $date . '%')
                                ->and_where('username', '=', $user)
                                ->execute()->as_array();
                $count = Arr::flatten($count);
                $result[$date][$user] = $count['total'];
            }
        }
        return $result;
}
[PHP] pobierz, plaintext 

[tehaha]

najlepiej zacznij od początku - napisz co potrzebujesz uzyskać, a nie to co wydaje Ci się rozwiązaniem, bo najczęściej takie kombinacje są wynikiem źle zaprojektowanej bazy danych

[matino]

Baza zaprojektowana jest dobrze, wydaję mi się tylko, że można to zrobić dużo prościej...
Rozwiązaniem ma być ilość wystąpień loginu usera dla danych dat.

[tehaha]

eh.. nie ma to jak ludzie, którzy nie pozwalają sobie pomóc, słuchaj nikt nie siedzi w Twojej głowie i nikt nie wie co Ty sobie wymyśliłeś, chcesz pomocy? ok, ale podaj strukturę tabel i napisz dokładnie co chcesz z nich pobrać, zapytanie w pętli to bardzo złe rozwiązanie i na 99,9% można to rozwiązać przy pomocy kilku zapytań, bo chociażby zamiast w pętli można dać WHERE IN() a porównanie daty przerzucić na php, ale ciężko powiedzieć coś konkretnego skoro nie podałeś żadnych konkretnych informacji

[matino]

Struktura:

[SQL] pobierz, plaintext 
CREATE TABLE `users` (
  `id` tinyint(1) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `username` varchar(32) NOT NULL DEFAULT '',
  `password` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  UNIQUE KEY `uniq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TABLE `sprawy` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `create_date` int(11) UNSIGNED NOT NULL,
  `username` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
[SQL] pobierz, plaintext 

W tabeli `sprawy` podałem tylko niezbędne kolumny.

Dane wjeściowe:
Data początkowa i data końcowa w formacie unix timestamp

Cel:
Znaleźć do ilu spraw, dla wszystkich dni zawartych pomiędzy datami wejściowymi, został przydzielony każdy z użytkowników z tabeli users.
Generalnie chodzi mi o wyświetlenie tabeli jak poniżej:

[HTML] pobierz, plaintext 
Data   |   jan_kowalski   |   piotr_nowak   |    zdzisio_misio
2011-06-11   |   5          |          3            |           6
2011-06-12   |   7          |          4            |           2
2011-06-14   |   1          |          6            |           7
[HTML] pobierz, plaintext 

[tehaha]

Baza zaprojektowana jest dobrze

No i właśnie nie...cała komplikacja bierze się ze złego typu kolumny w bazie danych, dałeś datę jako INT i nie możesz po niej grupować bo pewnie jest tam inny timestamp dla każdego rekordu.
1. w sprawy powinno być id użytkownika a nie jego nazwa
2. data powinna być jako date

Wtedy wszystko pobierasz jednym zapytaniem, grupując po 2 kolumnach:

[SQL] pobierz, plaintext 
SELECT a.id, a.date, COUNT(a.id) AS num, b.name FROM sprawy a LEFT JOIN users b ON a.user_id = b.id GROUP BY date, user_id
[SQL] pobierz, plaintext 

sprawdziłem u siebie zapytanie zwraca liczbę spraw dla użytkownika i datę w php bardzo łatwo wyświetlisz to w takiej tabelce jaką chcesz uzyskać

zapytanie zwróciło mi taki wynik:

[HTML] pobierz, plaintext 
ID        DATE          NUM      NAME
1 	2011-06-11 	3 	Tomek
4 	2011-06-11 	2 	Stefan
6 	2011-06-11 	3 	Mietek
9 	2011-06-12 	1 	Tomek
10 	2011-06-12 	1 	Stefan
11 	2011-06-12 	1 	Mietek
12 	2011-06-13 	4 	Tomek
16 	2011-06-14 	1 	Tomek
17 	2011-06-14 	4 	Stefan
[HTML] pobierz, plaintext 

Ten post edytował tehaha 18.06.2011, 16:52:23

[matino]

Właśnie problem jest w tym, że data jest używana również w innych częściach aplikacji, gdzie znacznie wygodniej jest operować na INT niż na DATE.
Id też wolę nie wstawiać, bo jeśli skasujesz użytkownika, to nie dowiesz się jaki username odpowiadał jego id.
Mimo wszystko dzięki za poświęcony czas

[tehaha]

no to dołóż drugą kolumnę z datą, w php łatwo napiszesz skrypt którzy stworzy date dla już obecnych rekordów i wtedy będzie po kłopocie, bo tak to przy dużej ilości dat i użytkowników będzie masakra

[matino]

Dzięki, o to mi chodziło

PS. Obeszło się nawet bez drugiej kolumny

[SQL] pobierz, plaintext 
SELECT username, FROM_UNIXTIME(create_date, '%Y-%m-%d') AS date, COUNT(id) AS num FROM sprawy GROUP BY date, username
[SQL] pobierz, plaintext 

Ten post edytował matino 18.06.2011, 18:28:50