Hallo zusammen,
dieses Thema wurde hier schon einmal behandelt:
Mir ist der Fehler aber auch in den folgenden Versionen aufgefallen. Eine Lösung habe ich auch gefunden.
Hier ist erstmal der Query, um den es sich dreht:
SELECT n_fn,org_name,account_id AS user_id,
(
SELECT CONCAT(cal_start,':',egw_cal_user.cal_id,':',cal_recur_date)
FROM egw_cal_user
JOIN egw_cal_dates on egw_cal_dates.cal_id=egw_cal_user.cal_id and (cal_recur_date=0 or cal_recur_date=cal_start)
JOIN egw_cal ON egw_cal.cal_id=egw_cal_user.cal_id AND egw_cal.cal_deleted IS NULL
WHERE cal_user_type='u' and cal_user_id=account_id and cal_start < UNIX_TIMESTAMP(NOW())
order by cal_start DESC Limit 1
) as last_event,
(
SELECT CONCAT(cal_start,':',egw_cal_user.cal_id,':',cal_recur_date)
FROM egw_cal_user
JOIN egw_cal_dates on egw_cal_dates.cal_id=egw_cal_user.cal_id and (cal_recur_date=0 or cal_recur_date=cal_start)
JOIN egw_cal ON egw_cal.cal_id=egw_cal_user.cal_id AND egw_cal.cal_deleted IS NULL
WHERE cal_user_type='u' and cal_user_id=account_id and cal_start > UNIX_TIMESTAMP(NOW()) order by cal_recur_date ASC, cal_start ASC Limit 1
) as next_event
FROM egw_addressbook
WHERE account_id=273
So, wie er da steht braucht die Abfrage über 40 Sekunden. Wenn ich aber den straight_join nutze, geht die Abfragezeit runter auf unter 2 Sekunden, was durch exzessive Benutzung selbigen nochmals auf fast eine Sekunde reduziert werden kann.
SELECT n_fn,org_name,account_id AS user_id,
(
SELECT CONCAT(cal_start,':',egw_cal_user.cal_id,':',cal_recur_date)
FROM egw_cal_user
STRAIGHT_JOIN egw_cal_dates on egw_cal_dates.cal_id=egw_cal_user.cal_id and (cal_recur_date=0 or cal_recur_date=cal_start)
JOIN egw_cal ON egw_cal.cal_id=egw_cal_user.cal_id AND egw_cal.cal_deleted IS NULL
WHERE cal_user_type='u' and cal_user_id=account_id and cal_start < UNIX_TIMESTAMP(NOW())
order by cal_start DESC Limit 1
) as last_event,
(
SELECT CONCAT(cal_start,':',egw_cal_user.cal_id,':',cal_recur_date)
FROM egw_cal_user
STRAIGHT_JOIN egw_cal_dates on egw_cal_dates.cal_id=egw_cal_user.cal_id and (cal_recur_date=0 or cal_recur_date=cal_start)
JOIN egw_cal ON egw_cal.cal_id=egw_cal_user.cal_id AND egw_cal.cal_deleted IS NULL
WHERE cal_user_type='u' and cal_user_id=account_id and cal_start > UNIX_TIMESTAMP(NOW()) order by cal_recur_date ASC, cal_start ASC Limit 1
) as next_event
FROM egw_addressbook
WHERE account_id=273
1.8 Sekunden
1.2 Sekunden, wenn “JOIN egw_cal ON” durch “STRAIGHT_JOIN egw_cal ON” ersetzt wird.
Es steht also das Richtige da, nur das MariaDB in unserem Fall es vorzieht, die Tabellen in umgekehrter Reihenfolge abzuarbeiten:
+------+-------------+-----------------+-------+---------------------------------------------------------+------------------------------------+---------+---------------------------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------+-------+---------------------------------------------------------+------------------------------------+---------+---------------------------+--------+-----------------------------------------------------------+
| 1 | PRIMARY | egw_addressbook | const | egw_addressbook_account_id | egw_addressbook_account_id | 5 | const | 1 | |
| 3 | SUBQUERY | egw_cal | ref | PRIMARY,egw_cal_deleted | egw_cal_deleted | 9 | const | 155996 | Using where; Using index; Using temporary; Using filesort |
| 3 | SUBQUERY | egw_cal_dates | ref | PRIMARY | PRIMARY | 4 | egroupware.egw_cal.cal_id | 1 | Using where; Using index |
| 3 | SUBQUERY | egw_cal_user | ref | egw_cal_user_id_recur_date_type_id,egw_cal_user_type_id | egw_cal_user_id_recur_date_type_id | 4 | egroupware.egw_cal.cal_id | 1 | Using index condition; Using where |
| 2 | SUBQUERY | egw_cal | ref | PRIMARY,egw_cal_deleted | egw_cal_deleted | 9 | const | 155996 | Using where; Using index; Using temporary; Using filesort |
| 2 | SUBQUERY | egw_cal_dates | ref | PRIMARY | PRIMARY | 4 | egroupware.egw_cal.cal_id | 1 | Using where; Using index |
| 2 | SUBQUERY | egw_cal_user | ref | egw_cal_user_id_recur_date_type_id,egw_cal_user_type_id | egw_cal_user_id_recur_date_type_id | 4 | egroupware.egw_cal.cal_id | 1 | Using index condition; Using where |
+------+-------------+-----------------+-------+---------------------------------------------------------+------------------------------------+---------+---------------------------+--------+-----------------------------------------------------------+
Im Addressbuch tritt das Problem beim Öffnen von Benutzern genauso auf.
Mit freundlichen Grüßen aus dem Rheintal,
Carl Christian Graf
