Mariadb – Json Column configuration

I'd suggest you split this large column off into its own table. While you're there take out any other columns which are only used once in a blue moon. (The smaller your row the faster your queries will run.)

I'll take your current table as MyData. Rename it to, say, MyData_Crucial. Let's call the new table MyData_Sometimes. Create this view:

create view MyData
select
    <all the columns>
from MyData_Crucial
inner join MyData_Sometimes
    on <primary key> = <primary key>

Now, assuming your optimizer is at least a little bit clever, you will not need to change your application code at all.

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.