658,142 questions
0
votes
0
answers
68
views
Why does the SUM() result change when moving a WHERE filter into a subquery in MySQL?
I have a vehicle rental database. I want to get the name, phone number, and total spending of clients who have rented a BMW or a Honda at the branch with the highest number of registered vehicles.
I'...
-2
votes
2
answers
50
views
Updating BINARY LOWER/UPPER queries to CAST
I'm using this query to identify misstyped usernames:
SELECT * FROM import_bk
WHERE
(
bk_name = BINARY LOWER(bk_name) OR
bk_name = BINARY UPPER(bk_name) OR
bk_name REGEXP '[0-9]'
)
But I always get ...
- 1,081
-11
votes
0
answers
109
views
how to improve performance COUNT distinct from 13.6 million rows scanned [closed]
13.6 million rows being scanned for COUNT(DISTINCT quiz_analytic_id). This is the real issue. COUNT(DISTINCT ...) is expensive because MySQL cannot use index statistics to shortcut it
-2
votes
0
answers
63
views
Why does COUNT(*) return a different result than COUNT(column_name) in a LEFT JOIN query? [duplicate]
I am trying to count how many orders each customer has. I noticed that COUNT(*) and COUNT(o.order_id) return different results and I don’t fully understand why.
Here is a simplified example:
CREATE ...
- 105
-2
votes
2
answers
124
views
Why after importing a CSV, it is stored alphabetically in the database table?
Why is my CSV file imported into the database alphabetically by the first column product_key? Rather than imported simply line by line, from first line in the CSV to the last line?
My CSV is not ...
- 104
-4
votes
0
answers
43
views
MySQL crashes when heavy operation happens or when I reinitialize DB [closed]
I work in a large scale java project. Whenever I drop multiple DBs using a script my MySQL instance crashes. When I reinitialize the DB and start my project or multiple data entry or schema creation ...
- 1
Best practices
0
votes
3
replies
79
views
How to connect MySQL Workbench to Power BI (MySQL 8.0) – connection error
I am working on a project and trying to connect MySQL Workbench to Power BI.
My setup:
MySQL version: 8.0.41
Tool: MySQL Workbench
Power BI Desktop (latest version)
What I tried:
Open Power BI ....
-2
votes
0
answers
59
views
Public Key Retrieval is not allowed [duplicate]
I tried to connect to
curl -X POST -H "Accept:application/json" --data @debezium-mysql.json http://localhost:8083/connectors
debezium-mysql.json
{
"name": "mysql-...
- 169
1
vote
1
answer
66
views
Privileges for user-defined functions in join predicates
I am trying to use user-defined function f() in a join predicate of a view v. Users will receive SELECT privileges on the view but no privileges on the function. The intention is that users inherit ...
- 33
0
votes
0
answers
30
views
MySQL 9.1.0 Windows - component_keyring_file fails to load from manifest file (Unknown error 1126, 3529) [migrated]
I'm trying to set up encryption at rest on MySQL 9.1.0 on Windows using component_keyring_file.
Setup:
MySQL 9.1.0 installed at C:\MySQL\mysql-9.1.0-winx64\
OS: Windows 11
Running MySQL as a service (...
- 11
1
vote
2
answers
104
views
How to rename a column with special characters in MySQL
How to rename the column when there is a special character? When I try to run the code below, I get the following error:
Unknown column 'Actual gross' in 'field list'
ALTER TABLE practice.practice1 ...
- 13
0
votes
0
answers
71
views
Only store one result from PDO (SQL) in a variable [duplicate]
I always use this method, for example, to get just a single value. It’s obviously not the most elegant solution, but it works.
Is there a shorter way to do this without a fake loop? And the result has ...
- 1,081
Best practices
0
votes
5
replies
77
views
Query to calculate %diff. between two rows on two tables MySQL
I have 2 tables 2025 and 2024. The columns are the same. I want to calculate the % diff. (+/-) between the same rows on the different tables. E.G., population of USA in 2025 compared with 2024. On a ...
- 1
1
vote
1
answer
86
views
Run a MySQL command from a bat file without prompting for a password
I am trying to issue a MySQL command from a windows bat file without prompting for a password. I can get the command to work if I provide the password in the command string, but for security reasons, ...
- 11
Best practices
1
vote
1
replies
89
views
How do developers usually handle database optimization for growing WordPress sites?
I’m working with a WordPress website that has been running for a few years and the database size has grown significantly. I noticed that some queries and page loads are slower than before.
While ...
