CODEWITHSUNDEEP
mysqlsqlindexing
Computation Smomputation
Computation Smomputation

Reputation: 169

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.

Upvotes: 1

Views: 217

Answers (3)

Frederic
Frederic

Reputation: 1028

I would suggest you to create a column to hold the hash of your WLANTYPE.

add index to the hash's column and a trigger to set it in insert / update..

and change your query a bit for:

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, max(WLANTYPE) as WLANTYPE
FROM wifi_log 
GROUP BY FileTime,UnitID,WLANTYPEHash
ORDER BY FileTime DESC;

see fiddle:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=16e86ea77eb242d2339d6050a1772b60

Upvotes: 0

Rick James
Rick James

Reputation: 142453

I have a technique for approximating COUNT(DISTINCT..) from summarized data. Could you build daily summaries of the data? Then roll up the data for the totals? Such is easy for COUNT (sum of counts) and SUM (sum of sums), but rather tricky for 'uniques'. It gives only approximations, usually within 1% of the exact result. Here is an overview of the technique: http://mysql.rjweb.org/doc.php/uniques

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562721

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 NULL\G

*************************** 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.

Upvotes: 1

Related Questions