Questions tagged [mysql]
MySQL is an open-source, relational database management system.
521
questions
3
votes
2
answers
180
views
Optimistic locking vs separate lock store for background workers
I need to compute the value of a row R on a table based on rows queried on another table B.
I need to do it in a queue worker because the computation of the value can be slow. A message is queued ...
CommunityBot
- 1
1
vote
2
answers
154
views
Do we have 2 logical query processings, one with indexes and one without indexes?
In the book "Inside Microsoft® SQL Server® 2008: T-SQL Programming" the behaviour of a sql query is explained. The following picture is taken from the book. I have some questions about the ...
- 10.7k
0
votes
5
answers
850
views
Is it reliable to compress database backups with git?
I'm working as an intern at a fund. I spent the last month building a website for internal use, and now I think it's a good time to set up a backup scheme for the MySQL database at its backend. Funny ...
- 11
0
votes
2
answers
126
views
How to handle time differences in MySQL
I'm facing a problem with handling different timezones in MySQL. I have looked into similar questions that were previously raised, but I couldn't find a proper answer.
The issue I'm encountering is as ...
- 149
65
votes
5
answers
37k
views
Is backing up a MySQL database in Git a good idea?
I am trying to improve the backup situation for my application. I have a Django application and MySQL database. I read an article suggesting backing up the database in Git.
On the one hand I like it,...
- 629
4
votes
2
answers
8k
views
Polling versus push notifications
I have a hybrid web app for android. The webpage or website the app shows is in asp.net. The javascript for the webpage running in the webview of the app is sending out an ajax request every 10 ...
3
votes
3
answers
161
views
How to reliably determine changes in a DB table since some sort of "checkpoint"?
My scenario is fairly typical, but I can't figure out a good solution. I have a DB table (in reality several unrelated tables, but let's focus on one) where there is a fair deal of activity going on. ...
- 11
3
votes
2
answers
549
views
Is it a good practice to have two unique IDs for an SQL-Database?
Currently I want to create a SQL database effectively and "logically". Lately I read a lot about the issues that it is (mostly) not a good idea to define primary keys that can be seen from &...
- 7,422
15
votes
9
answers
8k
views
Can it be acceptable to construct SQL queries dynamically?
I know that as a general rule, you shouldn't construct SQL queries dynamically because of the possibility of SQL injection.
However, it could come in quite handy to break this rule and define for ...
- 177
4
votes
3
answers
7k
views
Is it conceivable to have millions of lists of data in memory in Python?
I have over the last 30 days been developing a Python application that utilizes a MySQL database of information (specifically about Norwegian addresses) to perform address validation and correction. ...
- 3,137
1
vote
2
answers
296
views
Is it a good idea to design a database based on the API response data types?
I am trying to design my database, where i have to save some response data given from an external api as a webhook.
This api will return me as well “referenceId” as an identifier, which i will use ...
- 133k
9
votes
4
answers
4k
views
How to store a fixed length array in a database
I have a mysql database in which I have drafts, each of which contains exactly 24 players the order of which matters. I am conflicted between having a drafts table with 24 extra columns for each ...
- 72.4k
-3
votes
2
answers
163
views
Does DigitalOcean offer enough resources to run a scalable MySQL cluster?
I have a Kubernetes instance from DigitalOcean that has 2 worker nodes and 1 load balancer. Now I want to create a MySQL managed database cluster for the app that will run on that Kubernetes.
Question ...
- 14.5k
5
votes
4
answers
1k
views
My father wants to learn PHP-MySQL to port his application. What I should do to help? [closed]
My father is a doctor/physician. About 15 years ago he started writing an application to handle his patient's medical records in his clinic at home. The app has the ability to input patient's medical ...
- 3,137
2
votes
1
answer
1k
views
Implementing transactional entity lockouts with Spring and JPA
Spring Boot/Java 8/MySQL here.
I have a widgets table in my MySQL DB that is modeled by a JPA entity like so:
@Entity
@Table(name = "widgets")
@Data
public class Widget {
@Column(name = ...
-2
votes
2
answers
112
views
How to filter and concatenate multiple sql files into one database [closed]
I have an issue where I have multiple databases from previous projects that I would like to combine into one large database. These databases are stored in .sql files. The issue is that I only need ...
- 14.5k
-2
votes
2
answers
56
views
How to serve customized content to each user, based on targeting criteria?
In my PHP Laravel application, I want to display special offers to users, depending on the quality of their house(s).
There are 4 Models: User, House, Offer, OfferTargeting.
Each User can have many ...
- 19
1
vote
0
answers
97
views
Backend Design: System requires both ElasticSearch and MySQL. In which scenario we fetch data from MySQL or ElasticSearch?
So, one of the backend system, having 5 resource types (tables) and some foreign keys as well, requires full text support for two of the tables.
Also, for some use-cases, we require inner-joins for ...
- 121
10
votes
2
answers
6k
views
Is indexing foreign keys a good practice?
Looking at DB tables created by a different developer I have noticed that whenever a table had a forein_key_id field/column, it was always an INDEX/KEY.
I am not sure if it was manually created, or ...
- 159
1
vote
0
answers
334
views
Design MySQL database to store user workout data for Workout App
I am building a fitness app and I'm trying to figure out the optimal way to store the data is a MySQL database.
I would like to have specific programs (for example, 'get jacked'), I would like a users ...
- 121
-3
votes
1
answer
55
views
Server performance and CMS scalability [closed]
I have been given the job of a colleague who has resigned and I don't know where to start. I hope you can give me some hints:
It is about a CMS made from scratch (Javascript/jQuery/PHP/MySQL) which ...
- 133k
0
votes
2
answers
93
views
Transferring data from 1 web app into another offline
I have a web app that will be used locally on 2 different site. The program is a Vue web app made with node.js and express, using MySQL database. Due to limitation (no internet in site 1), there is no ...
- 19
-1
votes
1
answer
100
views
Commit instead of rollback? [closed]
Simple example (PSEUDO CODE):
for (int i = 0; i < 100; i++) {
START TRANSACTION;
SELECT id, name FROM employees WHERE id = i;
IF (someFunction(id)) {
ROLLBACK;
...
- 11
-2
votes
2
answers
186
views
Avoid redundant field using JSON type in MySQL [closed]
I have experienc with designing relational dbs but first time attempting to store JSON in a mysql table. I am using mysql 5.7.8 which has the ability to store json data type as a column.
When I create ...
- 139
0
votes
3
answers
1k
views
How to generate URLs for a custom URL shortener?
Due to some issues with other shorteners like goo.gl (disabling my links for example) I want to create my own URL shortener.
I am looking to have a single table that will contain the following columns ...
- 3,162
7
votes
4
answers
1k
views
QR Codes as Booking Confirmations for Conference?
A client of mine is holding a conference and we have the task of creating a booking system for them. However they have requested that we use QR codes so that on the door, a person can simply present ...
- 75.9k
12
votes
2
answers
22k
views
Creating a separate table for images or adding "image fields" to many tables?
I need to create a database in which several tables have images.
For example, users have profile pictures and uploaded ones and products have many pictures, as well.
Is it better to have one SQL table ...
CommunityBot
- 1
10
votes
7
answers
30k
views
What is the best approach for database design with lots of columns?
I am writing a query based financial application. It lets the user to write complicated equations (much like WHERE part of an SQL query) and find companies matching those criteria.
For the above, I ...
- 167
23
votes
4
answers
11k
views
Store images in the database, or in files with a database link?
Is it appropriate to store the image files in the database? Or it would be better to store only the path of the file in the database, while keeping the file itself on the server?
Are there any other ...
- 11
-1
votes
1
answer
1k
views
Locking a DB table and waiting for writing/reading it from a web application
I'm working on a web application using Spring (Java and JPA + Hibernate) and I was wondering if there is a way of locking a MYSQL table and then when another web service (or even another thread from ...
- 12.1k
4
votes
5
answers
2k
views
How can you ensure order of execution in concurrent tasks?
Here is what I am specifically doing:
I have a thread-safe queue
One 'write' thread constantly writes to the queue with data that comes from another service
Multiple 'read' threads take from the ...
- 2,029
48
votes
2
answers
39k
views
What is the difference between btree and rtree indexing?
I've noticed on MySQLWorkbench that you can choose how to store your indexes before forward engineering your design. The storage types are:
BTREE
RTREE
HASH
Researching this, I found some ...
- 131
-2
votes
2
answers
144
views
Database Structure for The Best Efficiency
I want a database table that can hold all of the group chat messages from all of the group chats.
The max amount of users per group chat would be 20.
The max amount of group chats per user would 25-...
- 57.7k
77
votes
15
answers
30k
views
Why should passwords be encrypted if they are being stored in a secure database?
I have a web service. Right now, I have passwords stored in plain text in a MySQL table on my server. I know this isn't the best practice, and that is why I am working on it.
Why should passwords be ...
- 43.5k
0
votes
1
answer
72
views
Database schema for two different types of elements on map
I wrote a software for a customer that shows clients on a map (Google Maps).
I store the clients on a table on the MySQL database (table clients) and show them on the map using markers. Each clients ...
- 11.7k
0
votes
1
answer
392
views
Database normalization for grade management system
I am working on a grade management system web app for a university and I came up with this database schema
How the app work:
In the user table I am going to keep all the users data and the ...
- 1,775
-4
votes
1
answer
307
views
Design a login system
I have a task of designing login system with the requirement below:
If the user login over three times, lock account for the first 5 minutes. The next failed 3 times login-in will lock the user ...
- 15.2k
2
votes
3
answers
288
views
Is it best practice to always try to return a minimum number of rows from the database?
First-time poster so please be gentle :)
I have a rather large table in my database (MySQL in case it matters) that holds lat/lng values of certain points of interest around the world (table name: ...
- 43.5k
0
votes
0
answers
122
views
Fill Docker Container from existing remote database
I do have n nodes managed by Kubernetes, all of them running several containers. A container may contain a database alongside with an application. As containers should be stateless, all data within ...
- 101
0
votes
1
answer
220
views
Variable WHERE clause while avoiding sql injection
I want the user to be able to provide a custom WHERE statement against a mysql-backed platform... do I need to worry about any possible SQL injection attacks notwithstanding invalid sql? See the ...
- 16k
15
votes
6
answers
13k
views
Should I use DATE or VARCHAR in storing dates in MySQL?
Simply put, I have date and time attributes on an Orders table. The data types for these attributes are MySQL's DATE attribute. But everytime I echo the date and time in PHP it just gives me a string, ...
- 5,752
0
votes
0
answers
49
views
What is best approach to designing a local-to-cloud cloud-to-local Infrastructure?
I plan to have a cloud web application(CWA) and several local web application(LWA), let's assume 3 LWAs and 1 CWA. CWA === LWA.
This web application would consist of a database server, file storage ...
- 109
-2
votes
2
answers
304
views
What's better, using DB or Storage services to save videos?
I am creating a website that allows to teachers to uploading courses, what's better, to just use the DB or should I use storage services, and how could I know the size of the videos?
I am using AWS if ...
- 11
2
votes
0
answers
57
views
Scaling Order Management System for ecommerce
Tech Stack: I am using MySQL 8 with InnoDB engine. Application is built using Java with Spring & Hibernate. Database is hosted in AWS RDS and web apps in EC2.
I have two primary tables for ...
- 259
2
votes
2
answers
1k
views
Chat part of application - using UDP or TCP?
I'm trying to build a section of my app where two users can message each other. I've read about TCP and UDP and it seems like TCP is more suited due to ordered packet delivery. However, TCP requires a ...
CommunityBot
- 1
-1
votes
1
answer
704
views
Best practice to flatten JSON object and store it database
I'm fetching data from Clockify, flattening the JSON response, and storing it in the database where each property on the API response should have its own table's field.
I have read this article about ...
- 3,162
0
votes
2
answers
496
views
Application/Database design where two tables have both 1-to-many and 1-to-1 relationships
I have an application where the requirements are to show a list of (just for example) car manufacturers. When you click on one of the manufacturers, a list of car models show up. You can then click on ...
- 323
6
votes
5
answers
713
views
Should I use a relational datamodel or Cassandra with ColumnFamily based indexes?
Currently we are working on some storage problems for log data from various servers and communication message logs (HTTP(S), XMPP). There will be many write operations and for read operations we will ...
- 3,137
0
votes
1
answer
181
views
Does it make sense to have redundant DB fields to improve query performance, programming speed and code readability?
I am a bit torn about the database design for a solution. I have the following structure: (only the relevant fields are marked with #)
+------------------+
| activities |
|------------------|
| ...
- 136
0
votes
1
answer
562
views
Is deterministic SELECT possible without specifying an ORDER BY?
When we show a table with many thousands of records, we only show a small part of the total result set so as not to send too much data to the client at once. Clients can request more parts of the ...
- 38.2k