ייעול שאילתת MYSQL
-
@WWW עזוב שניה ביצועים, מה זה השאילתה המסובכת וההזויה הזאת?
למה לא פשוט:SELECT cdr.t_user, grp.group_id, sum(cdr.elapsed) / 60 as sum, COUNT(cdr.id) as count FROM cdr INNER JOIN re_grp as grp ON grp.reg_exp = cdr.t_user WHERE cdr.end_ts > UNIX_TIMESTAMP('2023-7-1 00:00:00') AND cdr.end_ts < UNIX_TIMESTAMP('2023-7-2 00:00:00') AND cdr.elapsed < 7200 AND grp.group_id = "14" GROUP BY cdr.t_user HAVING sum(cdr.elapsed) > 100זה לא מה שאתה צריך?
-
-
-
-> Limit: 200001 row(s) (actual time=31750.842..31750.842 rows=0 loops=1) -> Filter: (sum(cdr.elapsed) > 100) (actual time=31750.837..31750.837 rows=0 loops=1) -> Table scan on <temporary> (actual time=0.004..0.005 rows=1 loops=1) -> Aggregate using temporary table (actual time=31750.451..31750.453 rows=1 loops=1) -> Nested loop inner join (cost=3766093.75 rows=46610) (actual time=22060.691..31750.388 rows=1 loops=1) -> Filter: ((cdr.end_ts > <cache>(unix_timestamp('2023-7-1 00:00:00'))) and (cdr.end_ts < <cache>(unix_timestamp('2023-7-2 00:00:00'))) and (cdr.elapsed < 7200) and (cdr.t_user is not null)) (cost=3049122.50 rows=932200) (actual time=22042.090..31708.494 rows=11270 loops=1) -> Table scan on cdr (cost=3049122.50 rows=25176946) (actual time=1.345..29896.727 rows=26187820 loops=1) -> Filter: (grp.group_id = 14) (cost=0.67 rows=0) (actual time=0.004..0.004 rows=0 loops=11270) -> Single-row index lookup on grp using unique_index (reg_exp=cdr.t_user), with index condition: (grp.reg_exp = cdr.t_user) (cost=0.67 rows=1) (actual time=0.003..0.003 rows=1 loops=11270)אבל זה בלי אינדקס כרגע על CDR.
-
-
