Questions tagged [mysql]

All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

Filter by
Sorted by
Tagged with
1
vote
0answers
12 views

Many to many relationship, select rows based on values in list

I want to select group_runs based on a list of group_runners.player and I'm having issue getting rid of results that don't have all the values on that list. In this case group_runs with id = 2 shouldn'...
-1
votes
0answers
19 views

Index with mixed int and char columns is slow

I have been optimising a complex query with multiple joins; currently down to 0.003 seconds. But I have joined to a table with a mixed index (int, char), which tanks execution time down to 0.3 seconds....
0
votes
1answer
10 views

How to get percentage after grouping?

I have a table like this: person_id food data 1 bread xxx 1 bread xxx 1 fruite xxx 2 bread xxx 2 fruite xxx food can have only three values (bread, ...
0
votes
2answers
20 views

Finding “one of” entries to a composite key/index

Problem: Assuming I have a table like this: CREATE TABLE `test` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `a_id` bigint unsigned NOT NULL, `b_id` bigint unsigned NOT NULL, `c_id` bigint ...
0
votes
0answers
9 views

Join one row to multiple rows in another table

i have two tables "apartment" and rooms Apartment Table: id Apartment 1 abc Room Table: id apartment_id room_name 1 1 abc1 2 1 abc2 2 1 abc2 now I want to display 1 row in PHP but in ...
0
votes
1answer
9 views

MySQL - Call a View inside a Stored Procedure

Let's say, I have a table as follows tbl_1 id name status usertype flag ===============================================================...
0
votes
1answer
27 views

In which table should the receipt column be?

We have a system, when a user adds a new record, they have to pay a specific amount. But if they want to modify the existing record, they can update it only twice without having to pay. However, each ...
0
votes
0answers
12 views

How to setup replication of 150Gb mysql database

I have 150Gb database on running project. How to create replication for it without minimum downtime? All manuals say: lock database, export data, import data at new server, configure and start ...
0
votes
1answer
23 views

MySQL return rows ONLY if ALL the rows in another table have a specified value

I have two tables Table1 : efd_details( id ) Table2 : efd_detail_status( detail_id , status ) Now the data as following : select * from efd_details; Output : ID 1 2 3 4 5 select * from ...
0
votes
1answer
15 views

MySql ORDER BY slow with join, fast as 2 queries

Why is the following query slow but fast when I provide the values inline? select u.* from user u join user_group g on u.group_id = g.id where g.account_id = 1 order by u.id limit 10; -- takes ~30ms ...
0
votes
2answers
19 views

Passing parameters in MySql procedure with prepare execute

I am trying to write a procedure that has a location parameter, this parameter is later used in the queries inside the procedure. Also, I want to be able to limit the query results dynamically. I have ...
0
votes
0answers
28 views

Review of my Grade Calculator Database Design

I'm currently making a Grade Calculator which will be used by students of a university to calculate their own grades. I'd greatly appreciate feedback on the design I've made. Do you see any obvious ...
0
votes
1answer
18 views

Row Level Trigger for insert/update to a table

Given a table ITEM as below : CREATE TABLE ITEM ( ItemNo int not null, category varchar(20) not null, Quantity int not null, Price decimal(10,2) not null, Total decimal(10,...
0
votes
0answers
8 views

XAMPP MySQL service crash after reboot

Operating System: Window 10 64bit Video: https://youtu.be/HodTJxphn94 When I run mysql, it immediately exits. I think this happens every 3 months. Is there any way to solve this? 12:10:51 [mysql] ...
0
votes
0answers
7 views

Understanding Laravel relationships better

I want to make a simple project to understand better the Laravel database relationships. So I decided to make a movie storage project which is holding some movie names and some informations about that ...
0
votes
0answers
16 views

Encryption & lsblk error

Previously I was performing encryption as below: /sbin/cryptsetup --verbose -d - luksFormat /dev/md0 /sbin/cryptsetup --verbose -d - luksOpen /dev/md0 mysqldata Now I am skipping the complete ...
0
votes
0answers
16 views

Optimisation of MySQL procedure to generate tree

I have MySQL table like this |-----------------------------| |IDENTIFIER | POINT1 | POINT2 | |-----------------------------| | NAME1 | AA1 | BB1 | | NAME2 | AA1 | BB2 | | NAME3 ...
1
vote
1answer
31 views

Add constraint to table creation in SQL

I have to create a table Product with elements P, PType, PName, Price and Origin. I need to have the constraint that all products with PType "Meat" must have "Europe" as Origin. I ...
0
votes
1answer
37 views

Slow SQL taking almost 1s to execute with only 24000 records! (EXPLAIN included)

Any ideas how to improve and what the actual culprit is? records in product are only 24000 takes 0.85s to run Mysql 5.7 select `product`.* from `product` inner join `manufacturer` on `product`.`...
0
votes
0answers
9 views

Database not showing

I create a database in the terminal linux ubuntu,gave it all privileges,created the table,inserted the rows.When I open phpmyadmin the database isn't showing how do I connect them? I did use mysql -u ...
0
votes
0answers
11 views

InnoDB Format Row from DYNAMIC to COMPRESSED increase table size (and never decrease back)

I recently decided to make some experiment with compressed table format. Most of the time when I convert a table from DYNAMIC to COMPRESSED I get a disk space saving from 50% to 80%. However yesterday ...
1
vote
1answer
15 views

Does large amout of idle connections hurt performance of Mysql

We have applications that keep large amounts of idle connections (thoudsands). Does that hurt performance of the database? Is it worth it to lower the number idle connetions for performances sake?
1
vote
2answers
83 views

MySQL freezes every hour at the exact same time

We have an hard-to-understand issue with our production MySQL RDS 8.0.20 instance. Problem: Every hour, at the exact same time, at :00 we experience a database glitch. Sometimes it's big and ...
2
votes
2answers
36 views

Select maximum of a count in a grouped clause

I have the following tables: Vehicles(v͟i͟n͟, model,category) Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date) vehicleSold(saleID,v͟i͟n͟,salePrice) When I join these tables using: select YEAR(Sales....
-1
votes
0answers
18 views

mysqld $MYSQLD_OPTS fails when starting MySQL on Fedora

I installed MySQL using the officials rpm on the website (I'm on fedora). I have an error when I do systemctl start mysqld.service. So I checked systemctl status mysqld.service and got this : ● mysqld....
-1
votes
2answers
39 views

A quick way to ORDER all IDs randomly in MySQL [closed]

I need to assign random IDs to a table. Thus, I create a mapping table as CREATE TABLE t2 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, SourceID int(11) unsigned NOT NULL, UNIQUE INDEX(SourceID), ...
0
votes
0answers
14 views

1205 Lock wait timeout exceeded after switching to multi-primary topology in MySQL InnoDB Cluster

I have set up an InnoDB cluster using MySQL (version 8.0.23) Shell. I added three nodes to my cluster. At first, my topology was a single-primary topology where I had one master node and two slave ...
0
votes
0answers
23 views

Trouble with a query subselect

I am basically trying to merge 2 large queries together and can't get it to work. I have an almost working query, except I can't get the server to accept the p.products_id in the nested query. Using ...
-1
votes
1answer
35 views

How to partition on a UUID/GUID column in MySQL Table

I have a table which has 3 Billions rows and 6 TB data in MySQL Aurora and this is expected to grow very rapidly in future 50% increase in a year . CREATE TABLE `AUDIT` ( `ID` varchar(255) NOT NULL, ...
0
votes
1answer
23 views

mysqld_pre_systemd fails when starting MySQL on Fedora

I installed MySQL using dnf (I'm on fedora) and from the officials rpm on the website. Both had the same problem : I have an error when I do systemctl start mysqld.service. So I checked systemctl ...
0
votes
1answer
21 views

Count between dates and operate between rows - SQL

I try to count elements between dates and operate between rows agnostically. As an example, suppose the month starts from day 2 until day 2 of next month. The table has fields {timestamp, status} and ...
-1
votes
5answers
60 views

Is adding empty rows bad practice? [closed]

I'm struggling to find a definitive answer about saving empty records in a DB. I do realise the question might be a bit vague, but if it's required, I'm happy to modify the question and provide some ...
0
votes
2answers
20 views

How to profile MySQL during a period of time

I want to know during the execution of sysbench --time=100 ..., the bottleneck (function) of MySQL. So I want to profile MySQL when executing sysbench --time=100 .... I know by set profiling = 1;, I ...
0
votes
2answers
27 views

mySQL subqueries calculating winning percentage

I have a table where I store the data of a random dice game where you win if you get a sum of 7 between the two dices. Here's the table: id result dice1 dice2 player_id 1 LOSE 4 4 9 2 LOSE 4 5 7 3 ...
0
votes
2answers
25 views

Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type?

So the tables that I have are Certificates, Approvers, CertificatesApprovers, Categories, Approvees, Status, AdditionalFields, AdditionalFieldsValues. Many Certificates to Many Approvers relationship. ...
0
votes
1answer
16 views

Enabling mySQL replication using GTID after purging binlog

I am currently working to get GTID based replication established between mySQL 5.7.33 on 2 servers. Amidst this work, I ran into a disk space issue and purged the binary logs on the master. Now I have ...
0
votes
2answers
38 views

Moved site to new, similar server - same query is taking 10x longer?

Edit: Old server SHOW GLOBAL VARIABLES: https://pastebin.com/raw/8GPTpub0 And for the new (problematic) server: https://pastebin.com/raw/XnfbFzh7 This morning I moved my WP website from one server to ...
-1
votes
0answers
16 views

Why my rds instance has so high write iops?

the sum count of the tps is about 500 (Com_insert: 30, Com_delete: 3~4, Com_update: 450~500) while the write iops is 8k+ and I found that the Create_tmp_disk_table is nearly always 0 ...
0
votes
1answer
37 views

How to get the size of a SELECT statement in SQL?

Consider I have to do a SELECT, without knowing how big the rows are, and I would like to run a query to check the length of the response, to calculate whether I will be able to store the result in ...
0
votes
0answers
27 views

What to use **MariaDB** or **Mysql** [closed]

So, I want to upgrade my server database to either MariaDB or to Mysql8 and I want to know what is the best option to go for. TBC: my server is Unix based system and my websites are developed in PHP. ...
1
vote
2answers
23 views

group by left(col, x) text column - possible to index?

I'm looking for a possible index for the following: select left(name,10), count(*) from tab group by left(name,10) This is the table config: create table tab( id int(11), name text, primary key(id), ...
0
votes
1answer
36 views

MySQL - terrible performance for simple query on large table

I'm having huge troubles with the performance of a very simple indexed query on a quite large table. Configuration: 48 cores, NVMe Raid, 376GB RAM (144GB used) Mysql 8 (Percona server) Server ...
0
votes
1answer
13 views

changing the value of a column in a table based on the value of another column

I have the following table that is the "Class" table: class_size class_number student_id 2 4005 1 4007 2 4001 3 4010 I want the "class_size" column to be generated ...
0
votes
0answers
18 views

MySQL structure for many to many relationship [closed]

I have a titles table, titles are movies or series. I want to suggest users similar titles so there is a one-to-many relation in the title_suggestions table (title_id, suggestion_id which refers to ...
0
votes
1answer
11 views

Child, Parent and Grand-Parent relationship where a child may not necessary have a parent or a grand parent

Here, I have some problem representing a hostel-room-inmate entities. First an inmate is given a hostel seat, Then he is allotted a particular hostel, Then he is allotted a room. My approach is that ...
0
votes
0answers
12 views

SQL Server job analogue for MySQL [closed]

Does MySQL contain some features like SQL Server job for doing background work? Please, give a reference or an example of usage.
0
votes
1answer
24 views

How important is drive speed for a MySql server

I'm trying to invest in a self managed server for MySQL. My use case will involve querying a full text index of anywhere from 5m to 20m records. About 5 thousand full text searches per hour. Usually ...
0
votes
2answers
39 views

Delete from GROUP BY with multiple GROUP columns

I have a simple table consisting of id, channel_id, value and timestamp: CREATE TABLE `data` ( `id` int(6) NOT NULL AUTO_INCREMENT, `channel_id` int(11) DEFAULT NULL, `timestamp` bigint(20) NOT NULL, `...
0
votes
2answers
33 views

select insert query optimization on large volume MYSQL 8.x

Im trying to run this SELECT query from table bidPrice that has over 160 million rows into a second table called spreads_v2 INSERT INTO spreads_v2(SELECT bp1.timestamp, bp1.idExchangePair, bp1....
0
votes
1answer
15 views

'user' is not valid at this position, expecting an identifier

CREATE TABLE 'users' ( 'id' int(11) unsigned NOT NULL AUTO_INCREMENT, 'name' varchar(75) NOT NULL, 'password' varchar(100) NOT NULL, 'email' varchar(100) NOT NULL, PRIMARY KEY ('id'), UNIQUE KEY '...

1
2 3 4 5
403