0

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?

  • I only have 25000 records atm and tried with either type of index and the quickest selects I get with no index at all (20 ms quicker). Skeptical, but it could be an inaccurate test as the size of the table is not realistic (also it's not hammered by inserts as it will be) – Recct Jan 7 '15 at 16:56
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.

| improve this answer | |
  • Good to know yes. Same with the insertions, I don't know yet but a safe assumption is 20 inserts per second. The agent that inserts them is supposedly not affected by a slow performing DB and that agent's workload is more mission critical than a responsive DB. I'll have to look back to this when things kick off I suppose there could be a lot of 'variables' to consider. – Recct Jan 7 '15 at 17:24
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.