MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE wifi_log (
    WifiID int(11) NOT NULL AUTO_INCREMENT,
    UnitID int(11) DEFAULT NULL,
    ServerTime timestamp NULL DEFAULT CURRENT_TIMESTAMP (),
    FileTime bigint(20) DEFAULT NULL,
    WLANTYPE text DEFAULT NULL,
    MACSRC varchar(25) DEFAULT NULL,
    MACDST varchar(25) DEFAULT NULL,
    BSSID varchar(25) DEFAULT NULL,
    SIG int(11) DEFAULT NULL,
    ESSID text DEFAULT NULL,
    PRIMARY KEY (WifiID)
);
EXPLAIN
SELECT 
    COUNT(DISTINCT(MACDST)) AS MACDST,
    COUNT(DISTINCT(MACSRC)) AS MACSRC,
    COUNT(DISTINCT(BSSID)) AS BSSID,
    COUNT(DISTINCT(MACDST))-COUNT(DISTINCT(MACSRC)) AS UnitDIFF,
    UnitID, FileTime, WLANTYPE
FROM wifi_log 
GROUP BY FileTime,UnitID,WLANTYPE
ORDER BY FileTime DESC;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
wifi_log
null
ALL
null
null
null
null
1
100.00
Using filesort


ALTER TABLE wifi_log ADD COLUMN WLANTYPEHash Varchar(30);

CREATE INDEX group_by_fields ON wifi_log(FileTime,UnitID,WLANTYPEHash)
CREATE TRIGGER insertModelHash
BEFORE
  INSERT
ON
  wifi_log
FOR EACH ROW SET
  NEW.WLANTYPEHash = MD5(NEW.WLANTYPE)
EXPLAIN
SELECT 
    COUNT(DISTINCT(MACDST)) AS MACDST,
    COUNT(DISTINCT(MACSRC)) AS MACSRC,
    COUNT(DISTINCT(BSSID)) AS BSSID,
    COUNT(DISTINCT(MACDST))-COUNT(DISTINCT(MACSRC)) AS UnitDIFF,
    UnitID, FileTime, WLANTYPEHash, max(WLANTYPE) as WLANTYPE
FROM wifi_log 
GROUP BY FileTime,UnitID,WLANTYPEHash
ORDER BY FileTime DESC;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
wifi_log
null
index
group_by_fields
group_by_fields
137
null
1
100.00
Backward index scan