New answers tagged

3 votes

Index usage for NULL values

Short answer: It may. Long answer: IS NULL optimizes like = constant. (IS NOT NULL does not.) There are many reasons why the optimizer may shun the index and do a table scan, tossing rows that don't ...
Rick James's user avatar
  • 77.7k
1 vote
Accepted

Which indexes create to speed up a query with where

You should run these queries to check the key distributionn SELECT COUNT(1) FROM mytable; SELECT deleted,closed,stato,COUNT(1) rowcount FROM mytable WHERE id_my_table_type in (3, 5) GROUP BY deleted,...
RolandoMySQLDBA's user avatar
3 votes
Accepted

trivial query very slow on primary key on a PostgreSQL DB (instantaneous on MySQL)

I assume that you have an index on id. If not, you need one. Your problem is the 1e9. If you write a numeric constant with the scientific notation, it is considered to be of type numeric: SELECT ...
Laurenz Albe's user avatar
  • 50.6k
1 vote
Accepted

Why is Ignore Duplicate Keys not allowed on a Unique Key?

the difference between an Index with Is Unqiue on and a Unique Key is mainly cosmetic. No, the point is that unique constraints are just declarative entities, you simply declare that some fields or ...
sepupic's user avatar
  • 10.8k
-1 votes

Why is it so fast to create an index on a table?

Usually create a index on one column is much faster than you thought it should be. For example when you create an index on User.name, usually the column will not exceed 31bytes and row will not exceed ...
ramwin's user avatar
  • 99
1 vote
Accepted

Can't see table partitions after adding PK

problems When you create a clustered primary key on a heap, it transforms the original heap table that you created, by logically ordering the previously unordered structure by the key column(s) you've ...
Erik Darling's user avatar
  • 40.6k
0 votes

What's the most efficient UUID column type

Very late, I know, Vlad. UUIDv7 is excellent for database inserts as it supports decentralized generation as well as efficient clustered index insertion.
Andrew G's user avatar
  • 111
0 votes

Should you include the primary key in an index, if the column is used in a condition?

If you have several processes inserting concurrently, and you use sequence caching to speed it up, it is possible in some cases to have a lower id inserted after a higher id. Here's an explanation. If ...
bobflux's user avatar
  • 1,436
0 votes

mysql table tables_priv index is corrupted and the best possible way to repair it

To address the corrupted index in the tables_priv table manually, consider the following steps: stop MySQL, and remove tables_priv.MAD, start MySQL, and run REPAIR TABLE mysql.tables_priv;. Verify ...
Arun Pratap's user avatar
3 votes

Finding all the index corruptions in the entire Postgres DB

You can use the amcheck extension to check a B-tree index for corruption. For other index types, there is no alternative to rebuilding the index. You can run the following to check an index for ...
Laurenz Albe's user avatar
  • 50.6k
0 votes

Is it bad to have index space larger than data space?

Another answer to the question: "Why are your indexes larger than your actual data" is : my table had a clustered columnstore index that compressed the table. When I added a nonclustered ...
nimajv's user avatar
  • 101
0 votes

Performance difference between multiple tables and indexed single table

Single table, PRIMARY KEY(symbol, timestamp). That serves as a unique index, so there is no need to have a redundant INDEX(symbol, timestamp). Please include the main queries you intend to apply to ...
Rick James's user avatar
  • 77.7k
7 votes
Accepted

Unique index on computed column when contributing columns are already unique

It's a trade-off only you can really decide on. You'll already have a Split-Sort-Collapse operator combination added to any plans that update Discount or CodeNumber to avoid false transient unique key ...
Paul White's user avatar
  • 83.7k
0 votes

How can I tell if a full-text index is being used?

Extended Events Can Do It... Badly I've hunted for any Extended Event that fires when a full-text index is hit. I have only found two. fulltext_query_exec_stats fulltext_query_exec_stats seemingly ...
J. Mini's user avatar
  • 93
0 votes

MySQL FullText search on string shorter than 3 chars returns no rows

I tried @RolandMySQLDBA solution, but still no results. The query is simple: SELECT * FROM agreement a WHERE MATCH (a.title) AGAINST ('it*' IN BOOLEAN MODE) The table is MyISAM, the title column has ...
long's user avatar
  • 101

Top 50 recent answers are included