Query performance improvements

[ngegw]

I have come to the end of my SQL skills, and the Last date / Next date column is still too slow when hundreds of thousands of rows are involved.
I would appreciate any help speeding this up.

The current query is:

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()) AND egw_cal_user.cal_status != "R"
	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()) AND egw_cal_user.cal_status != "R"
order by cal_recur_date ASC, cal_start ASC Limit 1
) as next_event
FROM egw_addressbook
WHERE account_id IN ('36','35','31','33','30','5','24','18','14','34','22','28','29','23','26','25','27','7','8','9','12','13');

Anything I’ve tried including:

• any form of swapping out those subqueries for a groupwise max/min
• index on egw_cal_dates (start_date)
• index on egw_cal_dates (cal_id, start_date)

makes it slower.

Thanks,
Nathan

[ngegw]

And here’s the current EXPLAIN:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	egw_addressbook	""	range	egw_addressbook_account_id	egw_addressbook_account_id	5	""	22	100.0	Using index condition
3	DEPENDENT SUBQUERY	egw_cal_user	""	ref	egw_cal_user_id_recur_date_type_id,egw_cal_user_type_id	egw_cal_user_type_id	3	const	413622	9.0	Using index condition; Using where; Using temporary; Using filesort
3	DEPENDENT SUBQUERY	egw_cal	""	eq_ref	PRIMARY,egw_cal_deleted	PRIMARY	4	171_test.egw_cal_user.cal_id	1	50.0	Using where
3	DEPENDENT SUBQUERY	egw_cal_dates	""	ref	PRIMARY,egw_cal_dates_id_start	egw_cal_dates_id_start	4	171_test.egw_cal_user.cal_id	10	33.33	Using where; Using index
2	DEPENDENT SUBQUERY	egw_cal_user	""	ref	egw_cal_user_id_recur_date_type_id,egw_cal_user_type_id	egw_cal_user_type_id	3	const	413622	9.0	Using index condition; Using where; Using temporary; Using filesort
2	DEPENDENT SUBQUERY	egw_cal	""	eq_ref	PRIMARY,egw_cal_deleted	PRIMARY	4	171_test.egw_cal_user.cal_id	1	50.0	Using where
2	DEPENDENT SUBQUERY	egw_cal_dates	""	ref	PRIMARY,egw_cal_dates_id_start	egw_cal_dates_id_start	4	171_test.egw_cal_user.cal_id	10	33.33	Using where; Using index