r/mysql_query • u/jrstark24 • Jan 18 '22
r/mysql_query • u/[deleted] • Jul 28 '25
Correct SQL Clause Order
Correct SQL Clause Order:
SELECT – columns to retrieve
FROM – table to query
JOIN – join another table (optional)
ON – join condition (used with JOIN)
WHERE – filter rows before grouping
GROUP BY – group rows
HAVING – filter groups
ORDER BY – sort results
r/mysql_query • u/eklarter • Nov 14 '25
newbie coding question
I have two tables albums and songs. Songs contains particulars of songs including the album where it is found. Albums contains information regarding the location in my library. The join is to be on matching albumid/title with a where clause specifying album_id. my code looks like this:
SELECT title, artist_id , album_id , sequence, runtime, year, notes FROM songs, albums.slot INNER JOIN songs ON album_id = albums.title WHERE album_id LIKE "%high%" AND artist_id LIKE "%dyl%" ORDER BY album_id , sequence;
Executing the query, I get the following from phpmyadmin:
#1066 - Not unique table/alias: 'songs'
Can anyone help with my syntax?
r/mysql_query • u/stormosgmailcom • Sep 26 '22
How to run a procedure every hour in MySQL?
r/mysql_query • u/Organic-Isopod6377 • Sep 29 '22
Rollback/Commit does job but shows 0 records affected
Hi,
I am wondering when i run a query for update and then Rollback/Commit does job but shows 0 records affected, it shows 0 rows affected but it actually does the correct job. I saw some youtube video too and same thing. They are also showing how the rollback change to previous state but I found the output shows 0 rows affected. I don't get it. If its updating 2 records then rollback should show 2 records. why showing 0 but doing the job in back. Can someone help explaining? Thanks
r/mysql_query • u/Ts_3104 • Feb 16 '22
MySQL & Python
How can I implement mysql in my program that if I give it to someone else, they don't have to install mysql on their pc
r/mysql_query • u/[deleted] • Jan 17 '22
How to copy names from one table to another in specific case?
Hello everyone, I am building attendant system, and I have two tables (students- attend) So, all the students will be registered in student table let’s say we only have five students as shown in photo. When the student signed in, he/she will be inserted to attend table as showed in the photo and the word present will be written under attend table head. But I want when I click a button after the class finish to insert the rest of the student that didn’t sign in in this case (Sara and Zain) to attend table with the word absent. How I can do that. database structure
Thank you
r/mysql_query • u/Own-Safety-6084 • Feb 18 '22
What is the best way to create a database to store and retrieve this information
r/mysql_query • u/Ok-Law-5565 • Mar 09 '24
Big Query training classes
I am looking to take Big Query training classes as my company is moving from Access SQL. Any advice on good training available?
r/mysql_query • u/Dr-Double-A • Mar 08 '24
Need Help: Optimizing MySQL for 100 Concurrent Users
I can't get concurrent users to increase no matter the server's CPU power.
Hello, I'm working on a production web application that has a giant MySQL database at the backend. The database is constantly updated with new information from various sources at different timestamps every single day. The web application is report-generation-based, where the user 'generates reports' of data from a certain time range they specify, which is done by querying against the database. This querying of MySQL takes a lot of time and is CPU intensive (observed from htop). MySQL contains various types of data, especially large-string data. Now, to generate a complex report for a single user, it uses 1 CPU (thread or vCPU), not the whole number of CPUs available. Similarly, for 4 users, 4 CPUs, and the rest of the CPUs are idle. I simulate multiple concurrent users' report generation tests using the PostMan application. Now, no matter how powerful the CPU I use, it is not being efficient and caps at around 30-40 concurrent users (powerful CPU results in higher caps) and also takes a lot of time.
When multiple users are simultaneously querying the database, all logical cores of the server become preoccupied with handling MySQL queries, which in turn reduces the application's ability to manage concurrent users effectively. For example, a single user might generate a report for one month's worth of data in 5 minutes. However, if 20 to 30 users attempt to generate the same report simultaneously, the completion time can extend to as much as 30 minutes. Also, when the volume of concurrent requests grows further, some users may experience failures in receiving their report outputs successfully.
I am thinking of parallel computing and using all available CPUs for each report generation instead of using only 1 CPU, but it has its disadvantages. If a rogue user constantly keeps generating very complex reports, other users will not be able to get fruitful results. So I'm currently not considering this option.
Is there any other way I can improve this from a query perspective or any other perspective? Please can anyone help me find a solution to this problem? What type of architecture should be used to keep the same performance for all concurrent users and also increase the concurrent users cap (our requirement is about 100+ concurrent users)?
Additional Information:
Backend: Dotnet Core 6 Web API (MVC)
Database:
MySql Community Server (free version)
table 48, data length 3,368,960,000, indexes 81,920
But in my calculation, I mostly only need to query from 2 big tables:
1st table information:
Every 24 hours, 7,153 rows are inserted into our database, each identified by a timestamp range from start (timestamp) to finish (timestamp, which may be Null). When retrieving data from this table over a long date range—using both start and finish times—alongside an integer field representing a list of user IDs.
For example, a user might request data spanning from January 1, 2024, to February 29, 2024. This duration could vary significantly, ranging from 6 months to 1 year. Additionally, the query includes a large list of user IDs (e.g., 112, 23, 45, 78, 45, 56, etc.), with each userID associated with multiple rows in the database.
| Type |
|---|
| bigint(20) unassigned Auto Increment |
| int(11) |
| int(11) |
| timestamp [current_timestamp()] |
| timestamp NULL |
| double(10,2) NULL |
| int(11) [1] |
| int(11) [1] |
| int(11) NULL |
2nd table information:
The second table in our database experiences an insertion of 2,000 rows every 24 hours. Similar to the first, this table records data within specific time ranges, set by a start and finish timestamp. Additionally, it stores variable character data (VARCHAR) as well.
Queries on this table are executed over time ranges, similar to those for table one, with durations typically spanning 3 to 6 months. Along with time-based criteria like Table 1, these queries also filter for five extensive lists of string values, each list containing approximately 100 to 200 string values.
| Type |
|---|
| int(11) Auto Increment |
| date |
| int(10) |
| varchar(200) |
| varchar(100) |
| varchar(100) |
| time |
| int(10) |
| timestamp [current_timestamp()] |
| timestamp [current_timestamp()] |
| varchar(200) |
| varchar(100) |
| varchar(100) |
| varchar(100) |
| varchar(100) |
| varchar(100) |
| varchar(200) |
| varchar(100) |
| int(10) |
| int(10) |
| varchar(200) NULL |
| int(100) |
| varchar(100) NULL |
Test Results (Dedicated Bare Metal Servers):
SystemInfo: Intel Xeon E5-2696 v4 | 2 sockets x 22 cores/CPU x 2 thread/core = 88 threads | 448GB DDR4 RAM
Single User Report Generation time: 3mins (for 1 week's data)
20 Concurrent Users Report Generation time: 25 min (for 1 week's data) and 2 users report generation were unsuccessful.
Maximum concurrent users it can handle: 40
r/mysql_query • u/TemperatureShoddy647 • Feb 29 '24
Help me with the syntax
alter table users add column temp_created_at datetime; UPDATE users SET temp_created_at= STR_TO_DATE (created_at, '%d-%m-%Y %H:%i'); ALTER TABLE users DROP COLUMN created_at; ALTER TABLE users CHANGE COLUMN temp_created_at created_at DATETIME;
After this syntax an error is showing up
'while converting text format to datetime format for users table iam getting error : Error Code: 1411. Incorrect datetime value: '01-01-2013 20:59' for function str_to_date .'
r/mysql_query • u/joes47 • Nov 21 '18
How to swap to column values of a table and then save the changes in mysql?
r/mysql_query • u/No-Seaweed-7579 • Aug 27 '24
Need Suggestion in learning for Upskilling My Role
Hi, can anyone suggest or recommend where or which website will be good to self learn My SQL, I am currently looking for a role in data analyst and I am skiiled with Tableau, Alteryx and Advance Excel Thanks
r/mysql_query • u/Harry_pentest • May 18 '20
MySQL logs real time
MySQL logs
Trying to see real time logs in MySQL(to know which statements are called) when a user logs in to an application. A server where user logs in to an application validating his stored credentials on MySQL. How do I check that; trying to find which queries in MySQL are called during login process.
r/mysql_query • u/[deleted] • Aug 02 '23
On a linux server, how do you find out where/report on user queries that should not be appearing?
For example if queries are appearing on the backup server, how explain where its coming from outside of the person in charge(root user) of the examining backup server?
r/mysql_query • u/Revolutionary_Use587 • Jul 15 '23
Procedure/Function
How to provide stored procedure and functions access on DB1 to perticular user ?? Can I also provide on server which has super_ read_only/ Read_Only variable is ON?
r/mysql_query • u/lucian-12 • Jan 18 '21
[video] Making use of MySQL EXPLAIN ANALYZE
r/mysql_query • u/Alert-Ad-5918 • Oct 04 '24
Does anyone know what would be the best way to get replies to comments
Hey guys, i'm workiing on a comment section and im upto getting replies for comments and wondering what would be the best way to get a list of replies for each comments in php MYSQL. Need some help?
r/mysql_query • u/[deleted] • Jun 23 '23
How to extract values from column and update result in another column
Let's say I have table Table1 having columns as Col1 which has the data as A_B_C_D. I have to create 4 new columns and feed those columns this data as:
| Col1 | Col2 | Col3 | Col4 | Col5 |
|---|---|---|---|---|
| A_B_C_D | A | B | C | D |
Also, please note that i have more columns too in this table so i dont want to disturb that data, I Just want to create 4 new columns and feed them this data.
Please help me to create MySql query, how to do this? I have to use it in BigQuery and it also follows MySql syntax only.
r/mysql_query • u/Saren-WTAKO • Feb 22 '16
Why?
Why would you do that? mysqli_query or pdo_mysql is much better. Stop using mysql_query right now.
