TokuDB + MariaDB 10: should I use a clustered index

Question

I have a large table (200 Columns.. I know, but it wasn't my design) that will grow fast and I will have a lot of useless rows in it, identified by an IP (a varchar 32 column).

The table will get hundreds of thousands of rows per day and I will need to delete few thousand rows with a specific IP in that column. During the day at regular intervals (5 min perhaps) I will need to select rows and avoid rows with certain IP. The deletions I'll probably do in the evening not to put too much load on the DB.

Should I use a clustered index or a regular index on this column? TokuDB claim there's no performance loss unlike InnoDB but still we're talking 200 columns (a lot of them empty to be fair (or null or 0s)).

I will need to add more indexes as well on a few other varchar columns as well over which I'll perform selects. Some will have huge cardinality as they're "Timestamps with milliseconds".

Will I benefit or suffer from clustered indexes?

Answer 1

That's a tough question to answer without more schema and workload information, but the key benefit to a clustered secondary index is that it contains not just the indexed columns and all other columns in the table. "Normal" secondary indexes contain the indexed columns and the primary key (which is used to look-up the other columns for your SELECT STATEMENTS via the PK index).

The downside to a secondary clustered index is that all of the data is stored twice. Once for your PK and once for the clustered secondary index. If your data is compressible this may be less of an issue, but a clustered index certainly takes up more space on disk than an unclustered index.

Now for the benefit. If you are doing range scans, or your query needs multiple rows for the same IP address, a clustered secondary index will perform significantly better than an unclustered one. If your database is much larger than your cache then you'll save 1 IO per row returned, which can make a significant difference for your queries.

The insert performance on the table is affected by this choice, but you didn't mention if you have an insert performance issue.

Answer 2

@Recct and others in such situation

The best option is to use IP as 4byte (ip v4) or 9byte(ip v6) integer type. It decreases size of column and index. Their comparison as numbers,selecting ranges is much faster than as varchars.

The same situation is with Timestamp.

Thus cluster indexes like (ip,timestamp) are even more beneficial for queries like

Select *
From table
Where (not (ip=xxx) ) and (timestamp between yyy and zzz)

Clustered indexes for varchar will not give much profit in your case, because you can not use ranges for them. Thus you will expect processing queries in almost the same manner as with regular indexes.