MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

-1
votes
0answers
8 views

ID That Will Never Be Used

I'm writing some tests for a Django application and in these tests I need to provide an ID for the view in question. I also need to test for an ID that is not in the DB. At the moment I am using ...
0
votes
0answers
6 views

Mysql query hangs on import

I dumped all my databases in order to import them into a slave. mysqldump --all-databases > dump.sql The import seems to be very very slow. Checking the processlist, I saw a simple insert on a ...
0
votes
0answers
12 views

Can an index be added to a PRIMARY with a null key?

I have a query that takes a long time to return, roughly 12-15 seconds, which I'd like to optimize. The query below is the result of running the original query with SHOW WARNINGS. If I run EXPLAIN ...
0
votes
0answers
9 views

MySQL Count where all conditions evaluate to 1

What is the best way to get count for all the records, where case evaluates to one for all the conditions. I have the query as: SELECT uuid , COUNT( CASE WHEN (( dt = 1 or level0 = ...
0
votes
1answer
13 views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
0
votes
0answers
12 views

Can I extrapolate from the progress reporting in MyISAM ALTER?

I'm migrating a 300 million row MyISAM table to add some indexes. Just because it might be relevant, here's my ALTER statement: ALTER TABLE my_table ADD UNIQUE INDEX my_table_abcd (a, b, c, d), ...
0
votes
0answers
11 views

How to interface a database to a mobile app and a Pc client [on hold]

I have a background as an embedded programmer (avrs and pics) and I have recently been taksed by my employer to build a data acquisition network that (i)Reads data from sensors and transmits them ...
0
votes
0answers
10 views

linking a single table (comment) to multiple tables (article,link,attachment)?

the main idea is that i can post an article , link or an attachment and i can receive comments on each of these types of posts (the structure of the comment is the same) , the problem here is that i ...
0
votes
1answer
7 views

My connection to a Mysql DB has been blocked after spamming queries, for how long?

I am creating a web application that depends on querying a legacy DB without an API. It's MySql. Off course the lack of an API eventually got me in trouble. A feedback loop while testing the app ...
0
votes
0answers
16 views

Multiple user roles with different attributes

I need to design a database that has multiple users, with multiple roles. For instance, a user can be an admin, company, employee, or customer. Each user can have any number of these roles. Right now ...
0
votes
0answers
5 views

Will a covering index save a table hit if only the commoly-used length of a `text` field is indexed?

I would like to add a covering index for a table that contains a text field. The maximum size of the field that is in use is ~2000 ASCII characters, 95% is below 800 characters. I am considering ...
1
vote
1answer
20 views

Selecting, ranking and applying a point score based on rank

I am writing an MVP system based on votes from users. The table will look like vote | game | year --------- ------ ------ player 1 | 2 | 2015 player 1 | 2 | 2015 player 2 | 2 | 2015 ...
1
vote
0answers
14 views

Grouping results with left and inner join with zero counts

I have a query like SELECT `campaign_question_options`.`text`, COUNT(`campaign_submission_answers`.`answer`) as `count` FROM `campaign_questions` INNER JOIN ...
0
votes
1answer
19 views

Query working in phpMyAdmin but not in Java [on hold]

CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, description ...
0
votes
1answer
32 views

Why does MariaDB 10.1.2 blow away MySQL 5.6.22 in sysbench?

Problem Maria DB is doing roughly twice the number of transactions that mysql is using standard oltp benchmark. The VM and db's are both fresh installs from vendor apt repos with default configs. ...
0
votes
1answer
17 views

adding autoincrement to id already part of foreign key

I would like to add the autoincrement property for all my table's columns named id in a schema. However, most of them are part of a foreign key constraint. Is there any other way to do it without ...
0
votes
1answer
23 views

Would we see a performance increase by moving one table to another physical drive?

We have a database of about 600MiB. One table is particularly large and occupies about 70% of that space. We quite often have to query this table - sometimes as far back as a few years, so we can't ...
0
votes
1answer
16 views

Reusing covering index for fields in FROM, from another query's index

For the following query I have a covering index as so: SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent; ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id); Now I have ...
-3
votes
1answer
27 views

How to check table [on hold]

How to check which tables a user have access to on a server?
0
votes
0answers
21 views

How can I optimize a query with multiple left joins?

The working example that I have been provided with takes about 20 minutes to fully process. http://paste2.org/G8BZvNHe What are some basic strategies for making this more efficient? Edit: I've ...
0
votes
1answer
16 views

multiple table inner join didn't get expected values

Student_register table: regNo pro(HNDIT,HNDA,HNDFT) name start_day etc ... more columns Semester: regNo curSemmester(3,4,5) name The query I used to get values: SELECT ...
0
votes
0answers
20 views

Retrieve a list of users based on some conditions [on hold]

What am I doing wrong on this query? The main issue it's that the var_dump is returning null. global $wpdb; $users = $wpdb->get_results( $wpdb->prepare( "SELECT u.ID AS ...
0
votes
0answers
25 views

mysql trigger, let trigger recalculate throug whole table after correction of errors

Raised this question before but no answer found I have one table (table1) with energy data updated every 5 min I am using a trigger to calculate day values on every update and fill another table ...
0
votes
1answer
11 views

how to overcome these type of issues where null value inserted into primary key not null column in table

how to overcome these type of issues where null value inserted into primary key not null column in table
0
votes
0answers
16 views

Performance issue after reorganizing table in MYSQL

There is MYSQL(5.6.22-log Community Edition) database environment which has databases size varies between 1-40GB and using INNODB storage engine, it's serves for the several number of OLTP based ...
0
votes
1answer
24 views

Row size too large (> 8126)

I'm facing the following problem. Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, ...
0
votes
1answer
7 views

Alter doesn't show in processlist

I'm performing an alter. It's currently running on a large table (300,000,000). MariaDB [my_database]> ALTER TABLE my_table -> add INDEX a (x, y, z), -> add INDEX d (x); Stage: 1 of ...
0
votes
0answers
4 views

looking for mysql tablespaces introduction

What would be a good place to read about the advantages of splitting data across several MySQL table spaces (as well as limitations that come along with that) ?
0
votes
0answers
7 views

what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata

I would like to organize the MySQL tables of a complex application into some groups, to avoid looking at a one big bin of tables. One big bin is harder to administrate when e.g. I would like to reset ...
-3
votes
0answers
28 views

why i am getting “Table 'DATABASENAME.INTOusers' doesn't exist” actually this table present in db [on hold]

I am trying to INSERT values from temp_table table to another table users both are present in same database i am successfully performing operations on temp_table. when ever i am trying to insert the ...
0
votes
1answer
17 views

How to know if 'mysql index' are used or not in all database or table? [on hold]

I read similar topics without success :/ I'd like to know how can i determine which index are used in my database or table : DATABASE Is there any tools, or SQL to use on the database, which can give ...
0
votes
0answers
3 views

What is the way to store calculated value (CNXwindow) in same table (postcotoffbookings) of (Pricingmodel) Mysql Databse?

This is the image of calculated cnxwindow after running the query, so kindly let me know how can i store this value in the same table after calculation.
0
votes
1answer
16 views

MySQL profile on query “Creating Sort Index” using 75% of the total time

We are trying to figure how to optimize a query (taking around 100ms), and running profile we see Creating Sort Index using 75% of the total time. First, what exactly effects creating the sort index? ...
0
votes
1answer
19 views

Difficulty with nested query order & grouping

I'm trying to identify the count of objects a user updated during a given day, but only where the user was the final update. Each object can have multiple updates in a day from any user, but the ...
-1
votes
0answers
23 views

Database migration for Large Data Set from MySQL to NoSQL

I have an existing APP that has N types of products that is normalized with MySQL. Each types of products have over 500K skus, and one of them are in the in 2M currently. We are currently tracking on ...
2
votes
1answer
35 views

Compare the structure of two MySQL Databases

I have two MySQL databases that are very similar to each other. How can I find out the differences in tables, and the differences in columns in each table? the databases are in different schema. ...
0
votes
0answers
23 views

Update table with selected data from join of other two table

Hey i'm new bee for mysql , I require a help that where i'm going wrong and what better can be done Table A ID_No | Mobile_No -------------------- 1 | 9999999999 2 | 9888888888 ...
0
votes
1answer
24 views

Is there any way to graceful restart mysqld? [duplicate]

I think my question is impossible but I sure I do not know everything. In my opinion, when mysqld restart, some transaction is not still completed will be rollback on startup. Mysqld use undo log to ...
0
votes
0answers
17 views

Booking DB structure

I’m thinking to make a booking site ( like Expedia one) for about 10 000 hotels. I’m a new to Mysql. Everything that I think of, have too many joins and rows. As I understand, main and most heavy ...
0
votes
0answers
30 views

Return results that NOT EXIST

I am trying to retrieve the 'names' (license_list.name) that do NOT EXIST from a table by ID in a separate table (licenses) and the syntax I am using is: SELECT DISTINCT license_list.name FROM ...
0
votes
1answer
13 views

Query running slow on script and producing error 500 when run in phpmyadmin

I have a query that seems to take a good 30 seconds to execute even when the query produces no results. There are 4000 lines within the database and queries that produce all results run within ...
0
votes
1answer
16 views

How to modify the below script to take tables and its sizes for a particular databse to send via mail to a recipient?

SELECT top 10 t.NAME AS TableName, (SUM(a.total_pages) * 8)/1024.0/1024.0 AS TotalSpaceGB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN ...
-4
votes
0answers
29 views

Getting count on multiple fields from table [on hold]

Please take a look at this page Travelmob. As you keep filtering on the "Amenities" or "Experience" on the left side, it update the total count on all of the fields. How do they do that? (could it ...
0
votes
1answer
18 views

Insert All Columns from one table into another table MYSQL

I am trying to insert multiple columns with their data from one table into another table, without a common column (i would expect a cross product in the result). I am not sure how that's possible, ...
0
votes
0answers
18 views

recover deleted databse from mysql [on hold]

I deleted "MYSQL" database from my phpmyadmin yesterday at that time there was no problem but today it is not allowing me to open my mysql datasbe Is that any way to recover mysql database which I ...
0
votes
1answer
11 views

MySQL - Migrating from Master-Slave replication to Master-Master

We currently run 2 large mysql master-slave replication trees. Each tree is built with one master, several 'middle' slaves replicating from the master, and 'end' slaves replicating from the 'middle' ...
0
votes
0answers
8 views

MySQL replication error when an event is expired and dropped automatically

I have an event that ends at 2014-01-21 10:00:00, and a replication error was caused by drop event *** statement, coz the slave complains that the event does not exist at all. A little bit more ...
0
votes
0answers
12 views

MySQL out of Memory, but Only Through MSSQL Linked Server

I am getting an error "MySQL client ran out of memory" when trying to do some simple MySQL selects or inserts from Microsoft SQL through a linked server. It was working fine for a week or so, and for ...
0
votes
1answer
17 views

wild card for entry id mysql

So I have a blog and some of my entries are set to "members", but I want to set them to "everyone". I originally thought it was only a few entries and so I thought to change them manually using ...
0
votes
0answers
12 views

Aggregate query on indexed field slows down due to WHERE clause. Can I speed it up?

I have a MySQL InnoDB table with about 33 million rows. It has an indexed 'date' column. CREATE TABLE T ( `Id` int(11) NOT NULL AUTO_INCREMENT, `date` datetime DEFAULT NULL, `Version` ...