All Questions
4,061
questions
0
votes
1
answer
27
views
How to Handle Indexes for @MapsId in JPA: Should FK and PK Share the Same Index?
I am using the @MapsId annotation in JPA to map a foreign key (FK) to a primary key (PK). According to the @MapsId annotation, the FK and PK share the same value. However, when I checked the database ...
- 121
0
votes
1
answer
44
views
indexing is not working properly after upgrade mysql 8.0.28
The issue is coming after up gradation of mysql 8.0.28. I have a table called user_products. When I run the EXPLAIN SELECT query on it, the possible keys identified are user_id, productId, status, ...
- 49
0
votes
0
answers
28
views
MySQL Date Column Index (NO Datetime)
I have a table with a date column (NO datetime, date only), which I always use to retrieve information by YEAR using BETWEEN. I was wondering if it's more efficient (and faster) to add a column to ...
- 79
0
votes
1
answer
36
views
Why is this query using filesort?
I am looking at my slow query logs and trying to fix some of slow queries, I identified one to fix and ran EXPLAIN on it and noticed it using filesort
Why would the following query show "using ...
- 44.6k
1
vote
1
answer
27
views
Character and integer performance differences in MySQL clustered index
I would like to know the difference between character type and integer type when configuring MySQL clustered index.
If you look at the principles of clustered index, it's a sequential configuration.
...
- 13
0
votes
1
answer
35
views
MySQL 8 - All Parts of Spatial Index Must Be Null - But no nulls in column
I've added a POINT column to a rather large existing table. I had to set it null initially as required by MySQL. I then executed successfully:
update geoloc set geoPoint = ST_SRID( POINT( 0, 0 ), ...
-1
votes
2
answers
52
views
MySQL specify primary key sorting order in secondary index
In MySQL 5.7.25 and MariaDB 10.6.15, using InnoDB by default to create below table
create table t1(
id serial,
employee_id int not null,
name varchar(20),
status tinyint,
created_at datetime(3) not ...
- 31
-1
votes
1
answer
71
views
What is the best approach to indexing in Postgres (or any other SQL DB)
I have the following query:
SELECT
id,
RANK() OVER (
PARTITION BY user_id
ORDER BY
effective_date <= '${date}' DESC,
effective_date DESC,
created_at DESC
...
- 133
-1
votes
2
answers
74
views
Multi-criteria search query optimization in MySQL
I'm using MySQL 8.0.35 and I've two tables in my database: users and clients. Users can be searched by username, firstname, lastname, email, phone, document or even id. The clients table is used to ...
- 673
0
votes
0
answers
18
views
Dose it order by primary key when value of secondary index is identical
ref：
https://dev.mysql.com/doc/refman/8.4/en/order-by-optimization.html
if InnoDB table, the table primary key is implicitly part of the index. Dose it order by primary key when value of secondary ...
- 1
1
vote
0
answers
47
views
Why can't MySQL 8 use a fulltext index with MATCH AGAINST > ? in prepared statement but 5.7 can?
Consider the following SQL:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
...
- 129
0
votes
0
answers
38
views
MySQL: Index on a column that indicates status
I have a table named t_task and it may looks like this:
CREATE TABLE `t_task` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`status` TINYINT(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`) USING ...
- 13
2
votes
1
answer
60
views
In MySQL, is a leftmost prefix of a composite index just as performant as an non-composite index?
MySQL's documentation says:
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (...
- 3,504
0
votes
1
answer
72
views
Why is the index not used in the query execution plan?
To briefly summarize the problem I have, it is that the index does not work properly in queries, which is just a difference in the database.
I am currently using two databases. For convenience, we ...
- 69
0
votes
0
answers
53
views
Why using index caused more rows scanning?
Please take a look at the following query:
SELECT cc.number_card
FROM customers_cards cc
JOIN customers_card_psp ccp
ON cc.id = ccp.card_id
AND ccp.psp_id = '12'
AND ccp....
- 6,575