Try to convert your query to
SELECT e2.personId, COUNT(*) - SUM(e2.personId = e1.personId)
FROM entries e1
JOIN entries e2 USING (categoryId)
WHERE e1.personId = 1
GROUP BY e2.personId
It seems that MariaDB uses implicit STRAIGHT_JOIN... or plan builder cannot optimize the query correctly.
For query optimize it's enough to swap table copies. See ...
Some of this information like index depth can be found in DMF dm_db_index_physical_stats(). Some outer interesting information that can be found in it is number of used data pages and the fragmentation level. Personally I have used the following query to get the state of the indexes for current database
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
I don't think you need to reinvent the wheel, this is already done by many SMEs before you thought this as a problem. Please check solution offered by Mr. Ola Hallengren(MVP). You just need to download them and define your parameters which is also explained at his website. This is fairly easy, straight forward and very much reliable.
For index maintenance ...
The WITH clause comes before the WHERE clause in the CREATE INDEX syntax. You are not free to rearrange the order as you have attempted here.
This ordering is a little unintuitive to me, but that is how it works.
Significant gains? No, I shouldn't think so.
SQL Server's indexes are B-Trees. These have a hierarchical nature. To read a row the server starts at the root node of the index then steps through one or more intermediate nodes before reading the row's values via the leaf node. (Details vary between clustered & non-clustered indexes, and heaps.) The speed-...
@jjanes' answer on stackoverflow that I accepted
With the help of the btree_gist extension, you can include the
event_type and start_date columns into the GiST index along with the
event_position. However, the event_type won't be very useful in the
index as long as the restriction clause is something like event_type in (0, 1, 2). (But if the list only ...
where tsetmc_id = 41974758296041288
and created_at > '2020-06-20'
and created_at < '2020-06-31';
Optimal index, in this order:
When you started with the range value, it could not go past that to the 'id'. The simple rule is: Put the = columns first in the ...
A partial index typically doesn't need the columns from the WHERE clause in the index as well. You should put other columns that you retrieve in the queries that make use of that index in the column. e.g. the PK or something else.
So if you e.g. frequently use this query:
select pk_column, other_column
where num_col = 0
and bool_col = false;
Adding the nonclustered columnstore index allows for a batch mode sort in the second execution plan. This causes all of the processing to be done on one thread - so even though the query has a parallel plan, it's essentially running serially. You can see that by looking at the details of the different operators.
I reproduced your problem locally, here's ...
Determining indexes for tables is not easy. I can offer some general guidelines that will help, but the specifics are going to be up to you to determine through testing on your systems with your queries.
The first, and most important, index you set is your clustered index. You only get one, so you want to make it right. Further, the key on your clustered ...
A clustered index on a timestamp column (a temporal data type such as datetime, not rowversion) is often a good choice for audit tables. This will help avoid full scans when date range criteria are specified in queries and the incremental value helps improve buffer efficiency during inserts.
An additional non-clustered index on UserName will help avoid a ...
From your question, if you filter on Username you should add a non-clustered index on username.
Make sure you also use the include statement for all the columns you are selecting or joining on.
If the user query is also order by timestamp, you can create the index like this:
CREATE NONCLUSTERED INDEX IX_USER_AUDIT_Username
ON Username(Timestamp DESC, ...
There are lots of pitfalls.
Simply adding PARTITIONing will not improve performance.
Change most of the indexes if you add PARTITIONing.
Don't use unless you have a least a million rows
Only BY RANGE() is useful.
It is essentially useless to PARTITION BY RANGE(the-primary-key)
There are only 4 use case for partitioning.
A PARTITIONed table is likely to be ...
Can't be done.
To achieve the goal, you would need a 2-dimensional index. Such does not exist.
category_id = 30
AND created_at > 1592862179
That much is handled nicely with INDEX(category_id, created_at). But adding anything onto the end of the index definition will not benefit you
The only way to get the index to be also useful for the ...
INDEX(library_id, firstdateofinterval) will help the query run faster. Note that the column tested by = should be first and the range should be last.
Why not use the desired index? Because it won't get past the range. That is, all 4 of your indexes are equivalent to simply INDEX(firstdateofinterval) when you test for a range of dates.
When you used the &...
It's possible there is corruption in those system tables. Try running:
DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
On all the databases on this instance to check for corruption.
It's also possible you're encountering a SQL Server bug. Try checking the SQL Server error log for any error messages around the time you get that "a ...
You have to FORCE the index, the optimizer will only then use your wanted index, else you give it only a hint
BIN_TO_UUID(album_id) AS e,
firstdateofinterval AS p,
BIN_TO_UUID(territory_id) AS t,
SUM(playbacks) AS c1,
SUM(userplaybacks) AS c2,
SUM(userdownloads) AS c3,
SUM(usersingledownloads) AS c4
I can reproduce this if I do your steps exactly, creating the index before populating the table. But if I create the index after the table is populated, I can't reproduce it. That is because the index present during population (when it is not populated in order, the way the primary key is) becomes somewhat bloated. This bloat isn't a lot, but it is enough ...
I fixed this issue by following 2 steps:
Killing all long-running transactions and then running vacuuming.
By adding a multi-column index. In your last query plan, you are fetching 19236+232584 rows. 12 times extra from what you actually need. Filtering is not free and you've to fetch the actual row before throwing it away. Because of that, you end up ...
The numbers in EXPLAIN and SHOW INDEX and SHOW TABLE STATUS are approximate. Live with it. Even ANALYZE, in some situations, will be approximate.
COUNT(x) counts how many rows have x IS NOT NULL. Usually you want to say COUNT(*) to count all rows (after filtering by WHERE. If id is "unique", then there will be no difference between COUNT(id) ...
A basic database principle is to avoid duplication of data. Option 2 has 3 rows with essentially the same basic data. So, a vote for Option 1.
Option 1 is smaller overall, so another vote for it.
If you get much past 3 sorts, the problem gets messier.
You are doing SELECT id. Was that a simplification for this discussion? If you really are selecting only ...
Thanks @Akina for pointing me in the right direction: https://stackoverflow.com/questions/16732980/why-cardinality-value-in-mysql-indexes-dont-equal-distinct-count-for-column-val
Running ANALYZE TABLE my_table updated the cardinality to match index size.
First, I must emphasize that you should use UNIQUE when you need the uniqueness check, not for performance considerations.
That said, here are the small performance considerations:
INSERT checks for uniqueness before acknowledging the insert. This slows down the response time (a little) for INSERTs. A plain INDEX is delayed and does not actually get ...
There will be no difference for read operations, once written the entries in the index will be the same.
In theory there is a slight difference in update performance as the engine needs to enforce uniqueness in a unique index, but in reality this is one going to be at most a few CPU cycles per row difference so will be unnoticeable.
Only create a unique ...
I just tried using the XML index creation commands in-lined with a table creation, and it didn't work.
I stumbled upon this article that makes it look like trying to create non-relational indexes (XML, columnstore, or spatial) doesn't work with in-lining the index creation statement in the table definition.
I'll assume you know what a B-Tree is. In a B+-Tree the leaf nodes contain all the column of the rows. A compressed B-Tree uses some form of compression to fit more information on each page. Compression is desirable because it maximizes the amount of information passed for each data movement. In a disk-based system the movements are between disk and RAM. For ...