MYSQL Index strategy when no WHERE Clause

I have this table

CREATE TABLE gotrax1.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)
)

I need to run this query on it

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;

It is dog slow and does a full file sort. Normally I know to add an index following the order of a where clause. I have no idea how to do it with this query and this table to avoid the filesort. Any suggestions would be terrific thankyou.

Answer

You can’t create an index on WLANTYPE as it is, because if you try to index a TEXT or BLOB, you get this error:

ERROR 1170 (42000): BLOB/TEXT column ‘wlantype’ used in key specification without a key length

I would question whether you need WLANTYPE to be TEXT. Perhaps a shorter VARCHAR would be just as good.

alter table wifi_log modify wlantype varchar(10);

Then you can add a covering index:

alter table wifi_log add key (filetime,unitid,wlantype,macdst,macsrc,bssid);

Also get rid of the ORDER BY FileTime so you don’t have to sort the result. Sort the result after fetching the result in your application, if it isn’t already in the order you want.

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 NULLG

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wifi_log
   partitions: NULL
         type: index
possible_keys: FileTime
          key: FileTime
      key_len: 366
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index

The type: index in this explain report shows that it still has to scan the whole index, which is nearly as expensive as a table-scan. But that’s natural for your query, which needs to get counts from every row.

The advantage of making this an index scan may be that it has to examine fewer pages. One index, even on 6 columns, is smaller than the whole table.

Also getting rid of the filesort will help.