r/mysql_query • • Jan 18 '22

Learn to Import and Export sql files in MySQL work bench with very simple steps..!

Thumbnail
nutpamputhithu.com
0 Upvotes

r/mysql_query • • Jul 28 '25

Correct SQL Clause Order

1 Upvotes

Correct SQL Clause Order:

  1. SELECT – columns to retrieve

  2. FROM – table to query

  3. JOIN – join another table (optional)

  4. ON – join condition (used with JOIN)

  5. WHERE – filter rows before grouping

  6. GROUP BY – group rows

  7. HAVING – filter groups

  8. ORDER BY – sort results


r/mysql_query • • Nov 14 '25

newbie coding question

2 Upvotes

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 • • Sep 26 '22

How to run a procedure every hour in MySQL?

Thumbnail
devhubby.com
1 Upvotes

r/mysql_query • • Sep 29 '22

Rollback/Commit does job but shows 0 records affected

1 Upvotes

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 • • Feb 16 '22

MySQL & Python

1 Upvotes

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 • • Jan 17 '22

How to copy names from one table to another in specific case?

1 Upvotes

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 • • Feb 18 '22

What is the best way to create a database to store and retrieve this information

1 Upvotes

Learning relational databases and MySQL, to replace an excel based system of creating labour and material constituent costs for items. Want to display only the level 1 to 4 descriptions, but have a build-up to the rate item.


r/mysql_query • • Mar 14 '24

Location data rearranging

Post image
1 Upvotes

r/mysql_query • • Mar 09 '24

Big Query training classes

1 Upvotes

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 • • Mar 08 '24

Need Help: Optimizing MySQL for 100 Concurrent Users

1 Upvotes

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 • • Mar 07 '24

What is this? What is this used for?

Post image
1 Upvotes

r/mysql_query • • Feb 29 '24

Help me with the syntax

1 Upvotes

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 • • Nov 21 '18

How to swap to column values of a table and then save the changes in mysql?

1 Upvotes

r/mysql_query • • Aug 27 '24

Need Suggestion in learning for Upskilling My Role

1 Upvotes

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 • • May 18 '20

MySQL logs real time

1 Upvotes

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 • • Jul 26 '23

Typeform to MySql

Thumbnail self.sql
1 Upvotes

r/mysql_query • • Aug 02 '23

On a linux server, how do you find out where/report on user queries that should not be appearing?

1 Upvotes

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 • • Jul 15 '23

Procedure/Function

1 Upvotes

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 • • Jan 18 '21

[video] Making use of MySQL EXPLAIN ANALYZE

Thumbnail
youtu.be
1 Upvotes

r/mysql_query • • Jan 01 '21

How to install phpMyAdmin in webserver

Thumbnail
youtube.com
1 Upvotes

r/mysql_query • • Oct 04 '24

Does anyone know what would be the best way to get replies to comments

1 Upvotes

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 • • Jun 23 '23

How to extract values from column and update result in another column

1 Upvotes

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:

Col1Col2Col3Col4Col5
A_B_C_DABCD

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 • • Feb 22 '16

Why?

1 Upvotes

Why would you do that? mysqli_query or pdo_mysql is much better. Stop using mysql_query right now.