MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
7 views

mysql Quiz leaderboard filter by points, time taken

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken. ...
1
vote
1answer
23 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
0
votes
0answers
11 views

Ideas for Database schema for Automotive web app

For the last couple of days I have been searching all over Google for database schema for my automotive web application[like a marketplace of sort](in Laravel 5 PHP). I have tried many models some I ...
0
votes
0answers
42 views

Outer left join with WHERE loses row

The line below MUST be in query or the null rows when joining are dropped. Why is this? //If I don't add this line; I lose rows where items.name IS NULL $this->db->or_where('items.name ...
0
votes
0answers
25 views

MySql Innodb Database Slow INSERT Query to Remote Server

I have searched thoroughly on how to improve general performance of my database which is hosted on Hostgator. I have mostly come across performance tuning strategies which are catered to only local or ...
1
vote
1answer
22 views

Can you enforce uniqueness in MySQL based on column in foreign table?

I am refactoring one of my database tables and I've come up with an issue. Specifically, I have a table with four columns. We'll call the first three my.a, my.b and my.c. I have a uniqueness ...
0
votes
0answers
16 views

MYSQL - number of rows returned equals number of connections?

Does my query get sent to the database once and I get a list of all the results in one shot which I then loop through, or do I have to request the next row from the DB each time? Essentially, does ...
0
votes
1answer
34 views

MySQL command “SHOW DATABASES” returns invalid databases

I have a MySQL server with a number of databases on it - 16 if I'm not mistaken. When I run the SHOW DATABASES command I get 30 rows. I would have expected 17 rows: The 16 databases and ...
1
vote
1answer
49 views

MySQL Index Creation Internals

Right now there are two ways to build an index of a table in MySQL: Create the table structure first, then import the data and add indexes later. Create the table structure with indexes and then ...
1
vote
0answers
37 views

MySQL bulk deletions

I am currently looking at how to most efficiently delete rows from a table in bulk. The database I'm using is MySQL with innodb. The table I want to delete from can be any size, but will usually be ...
0
votes
1answer
15 views

Cannot overwrite file: /var/lib/mysql/ibdata1 when using Percona xtrabackup

I am receiving an error when I am trying to restore from a percona backup to my local mysql server. Cannot overwrite file: /var/lib/mysql/ibdata1 at /usr/bin/innobackupex line 2128. sudo ...
0
votes
0answers
12 views

how to do a backup and restore for a single database with mysql enterprise backup

I am Chinese, the documentation of mysql enterprise backup is hard to understand for me, and now I need to finish my job quickly, I wonder is that possible to backup a single database and restore it ...
3
votes
1answer
65 views

How to check foreign keys related to a table

How to see foreign keys related to a table in MySql? Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this: Error Code: 1217. Cannot delete or ...
0
votes
0answers
15 views

hashed password retrieval in mysql

here I am again with the next question! I hash a password as follows: mysalt=os.urandom(12).encode('hex') passhash =mysalt + pw hash =str( pbkdf2_sha256.encrypt(passhash)) How can I verify this ...
0
votes
0answers
13 views

What could cause 300+ connections from a simple query in MySQL?

I am retrieving results from a table in MySQL 5.5. When I run even a basic query on this table hundreds of connections are shown in the Server Status screen in MySQL Workbench. Even for a simple query ...
0
votes
0answers
7 views

Post categories model for a user submission wedbsite

I'm building an user submission site where users can make posts to content_types: Blog, Movies, Photos, Forums. Since these content types only have slight variances I would like to keep them in one ...
0
votes
0answers
18 views

Filesort when using view

I am sorry for asking, but I can't figure what i am doing wrong and filesort appears in my explain. The id column that I use for sorting in the following examples is a primary key... If I create ...
0
votes
0answers
13 views

Working on a blog creation website; should I be creating a new table for each blog, or keep everything on the same table?

So I'm working on this blog creation website as a side project. I don't actually expect it to become anything, but I want to make it as efficient as possible, even if just for practice. The idea is ...
-1
votes
1answer
49 views

Grouping based on the status, then count its product and the total based on its category ID

I have a table with product ID, category ID and status of the product. +---------+-------------+-------------+ | id | cat_id | status | +---------+-------------+-------------+ | 1 ...
0
votes
2answers
23 views

understanding mysqldump auto-backup performance issues and inner functionality

Is there a performance issue, if I run a cronjob in background with mysqldump --single-transaction with a 60 seconds interval? Will my original database keep functioning without an issue while the ...
0
votes
0answers
15 views

Optimized ways for defragment mysql database table

We have a database in MySQL which has more than 500 GB of data, and currently in the process of purging data and count might go beyond 1 million. As we know that purging might lead to high ...
2
votes
1answer
40 views

Query to flatten schema

I have the following tables and columns: Users id | other-non-important-columns Containers id | Users_id | other-non-important-columns Content id | Container_id | other-non-important-columns ...
2
votes
2answers
43 views

Optimal engine for small lookup tables in MySQL

I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly ...
0
votes
0answers
26 views

Need some help building a query to get info from several tables

I have this semi-complex table structure: And I need to return this data: { "id": "10111001", "rep_id": "30000001", "targets_id": "60011001", "brands_id": "10010001", "brand_name": ...
1
vote
1answer
23 views

How to get row count from all tables of a schema ( without using information schema )

The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.
2
votes
1answer
33 views

How MySQL Queries and Questions status variables are calculated?

I have a mysql server running on 5.6.19 version and I got confused with the status variables Queries and Questions. show global status like 'Queries%'; | Queries | 28058738 | ...
0
votes
0answers
20 views

MySQL Pivot table

I have a MySQL table with following columns ZipCode PartNo Price DateCaptured Example data rows 50070 |P1234 |57.90 |2015-05-05 05:05:05 50070 |P1235 |58.90 |2015-05-05 05:05:05 ...
0
votes
0answers
27 views

Mysql trigger on insert on another table

I have three tables all three have [product_id] column, what all i want to do is when there is new entry of quantity of respective [product_id] in table A and table B i want show the sum of the ...
0
votes
0answers
9 views

Removing per column and table collation and charset

I recently completed a transition of a database from CHARSET=utf8 COLLATE=latin1 to CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. During the process tables were migrated one at a time by altering their ...
0
votes
1answer
23 views

How to insert UNIQUE IDs values from several tables into events table

I have a table called events defined as follow: rep_id, hcp_id, brand_id, email_id and content_id is the IDs in other tables. event_type should be kind of array containing this values: [ ...
0
votes
2answers
43 views

insert into select with union needs derived table

Why do I need to do another select when I am trying to insert distinct values into a table? Also: is there a better way to do this? WORKS: INSERT INTO phppos_categories (name) SELECT category FROM ...
0
votes
1answer
15 views

Function for generate a huge and possible random relationship between tables

I have this two tables and N:M relationship between them: Let's said I am managed to create a lot of media and emails records but I need to create the relationship between them. I can do this ...
0
votes
1answer
61 views

Slow “CREATE TABLE” and “TRUNCATE” on MySQL / MariaDB

I am fighting with performance issues on a local MariaDB instance. Fun thing: I previously had problems with all my INSERT and UPDATE and DELETE queries. This got signigicantly faster (~40ms -> less ...
0
votes
2answers
45 views

how to store mp3 files in mysql database by using phpmyadmin?

first of all i am new to database, but i want a database for my site to store mp3 files in it so later i play them on my site by using jplayer. little though i know about database they said there are ...
-2
votes
0answers
11 views

PMA-SQL-ERROR while importing databse in xampp [closed]

While importing database in xampp i am receiving following errors SQL query: <!-- PMA-SQL-ERROR --> <div class="error"><h1>Error</h1> <p><strong>SQL ...
1
vote
2answers
27 views

MySQL High Severity Error [closed]

I am completely new to MySQL. I am not a programmer or anything. I was installing the program months ago, and forgot my root password, tried to fix things and it failed. I did a system restore and ...
0
votes
0answers
19 views

insert/update query running slow to specifi tables

I am monitoring MySQL client connections and queries run time. Found some insert/update queries to specific tables are slow than expected. I know the tables which are slow are big in size > 600K, i ...
0
votes
0answers
15 views

Searching & deleting content that starts & ends the same, but has random text in between

So my Wordpress sites been hacked & spuriously created a load of content in the posts which starts with & ends with . The only trouble is that the bit in between is random hyperlinks. Any ...
0
votes
1answer
30 views

What is wrong with the following Python code — trying to insert into a database?

This works in MySQL Workbench: insert into nametest (name,surname) values('John''Krailling'); I am trying a get similar result with Pycharm with the following code: import MySQLdb db = ...
1
vote
1answer
30 views

MySQL 5.6 Slaves Fail to Reconnect to older MySQL server when it goes down for backups

I have an existing replication setup with Masters and Slaves running MySQL 5.0. A long existing script runs at night which restarts MySQL on the master with skip-networking, runs a backup, and then ...
0
votes
0answers
17 views

Confused over table_open_cache

I've been reading about the table_open_cache and I'm slightly confused about how big it should be, I've seen conflicting advice. My DB is running on MyISAM and has 199 tables. I ran into an issue ...
0
votes
0answers
25 views

Mysql - too many Deletion - Insertion

I'm creating crons for my OLAP sysetm, I have a cron that deletes the records from the summery table first and then insert the records in it again, Say Deleting 1 month records, and ...
0
votes
0answers
31 views

Get the rank of a row based on a score I am generating from other field values

I am trying to figure out how I can get the rank of a row from a score I am generating from other field values. The score query looks something like this SELECT ((table2.field1*0.4) + ...
0
votes
0answers
41 views

Deadlocks due to foreign keys (MySQL)

We are in the process of creating a web application using mysql as a data source. We are using spring with hibernate as an ORM framework. We have hundreds of entities with relations (manyToMany \ ...
0
votes
1answer
23 views

querying a table to find minimum of average population across all cities [closed]

Consider the table (City) structure given below I need to query on this table: Query : the minimum of average population across all cities | Field | Type | +-------------+------+ | ID ...
2
votes
2answers
147 views
+100

how to increase speed of this mysql query?

How I can increase speed of this search query? This search bring news based on time and title and text. each item has own factor. SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS ...
0
votes
1answer
17 views

MySQL default user

I have mysql.user table with this content: select host, user, password from user; | localhost | root | *75267AEB7355CEEE80EAB92D19FF50095AD9BB3E | | linux | root | ...
1
vote
1answer
50 views

Keeping an updated balance (Accounting)

I have been struggling trying to find a way to properly and reliably maintain a balance for accounts in an order/payment system. Currently, the process consists of three tables. Below example is very ...
0
votes
0answers
13 views

How do you create a 'shadow' database in mysql

A while back, a friend told me of a tool that would allow us to "shadow" a production database using a local database. The term shadow may be incorrect (so please correct me if so) What I ...
1
vote
1answer
53 views

Ansible: How to change MySQL server root password by reprovisioning the server

I have provisioned my server with Ansible playbook. I've used the root/bedrock-Ansible playbook. One of the tasks was to set up mysql server together with mysql root user password. Now I urgently ...