For a general-purpose or OLTP design, the initial index design should be more conservative:
Clustered index on Primary Key.
Unique non-clustered index any other unique keys.
Index supporting each Foreign Key (where not already covered above).
Then, for very large tables, consider changing to a Nonclustered Primary Key and a Clustered Columnstore.
You need a function which extracts the IP addresses into an array. The regexp you gave doesn't work for me, so I came up with my own crude one, you will probably want to tweak it to suit yourself:
create or replace function extract_ip(text) returns text immutable language SQL as $$
select array_agg(x) from regexp_matches($1,'\d+.\d+.\d+.\d+','g')f(x)...
One thing stands out clearly for me - this warning in the XML query plan:
<PlanAffectingConvert ConvertIssue="Seek Plan" Expression="N'Public'=CONVERT_IMPLICIT(nvarchar(20),[r].[SecurityLevel],0)"/>
A little known speed killer in SQL Server is passing an nvarchar() parameter to use in an indexed seek against a ...
The missing index code is extremely simple in what recommendations in suggests. One thing is that it doesn't care about selectivity.
The reason to include columns is to cover a query. I.e., so you don't have to do a bookmark lookup for each row. If you have a rather high selectivity, then these lookups don't matter much, and it is extreme overkill to ...
I don't know how useful this will prove to be as I've not tested it out thoroughly, but you should be able to utilize Statistics Density Vector values to see what columns (and column combinations) will provide a higher likelihood of unique values. The script listed below basically pulls all the Density Vector values for all stats on a given table. This is ...
This is not specific to MySQL, it is about B-tree indexes in general.
Leaving aside the implementation details, you can imagine a B-tree index as a sorted list of the indexed columns with a pointer to the table.
So if you imagine a two-column index on (num1, num2), it would look somewhat like this:
num1 | num2 | pointer
It thinks it is going to find 78722, but it really finds 16, so that is going to lead to some bad plans.
When a value in the in-list is not present in the MCV list of the stats table, it guesses their frequency using the n_distinct value, which is probably way off (you didn't answer my question about that). The way it does this is to take the number of ...
For a LIMIT 1, Postgres may estimate it to be faster to traverse the index supporting the ORDER BY and just keep filtering until the first row is found. This is fast as long as more than a few rows qualify and one of those pops up early according to ORDER BY. But it is (very) slow if no qualifying row pops up early, or even a worst case scenario if no ...
The simple solution is to modify the ORDER BY condition so that the semantics are unchanged, but PostgreSQL cannot use the index any more:
SELECT * FROM mcqueen_base_imagemeta2
WHERE image_id IN ( 123, ... )
ORDER BY id + 0 DESC