MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
13 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
0answers
7 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 ...
-1
votes
1answer
20 views

How to check table

How to check which tables a user have access to on a server?
0
votes
0answers
14 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
9 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
17 views

Retrieve a list of users based on some conditions

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
17 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
9 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
20 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
3 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
6 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?

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
13 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
18 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
20 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
33 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
21 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
23 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
16 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
29 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 ...
1
vote
0answers
9 views

How can I split a table while maintaining a transition period?

Constraints: I'm working on a pair of legacy applications that both depend upon the same database. Worse yet, the staging/integration instances of each of these applications depends upon this ...
0
votes
1answer
15 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
17 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
17 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
11 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` ...
2
votes
0answers
12 views

Combined UUID Index + AI PK Insert Performance

I posted this in stackoverflow but realised it probably belongs here instead. Imagine the following table: CREATE TABLE `orders` ( `ORDERID` int(11) unsigned NOT NULL AUTO_INCREMENT, ...
0
votes
1answer
7 views

Exported CSV from MySQL doesn't appear in specified directory

I'm trying to figure out the syntax to export the results of a MySQL query to the desktop as a CSV file via SSH. Here's what I'm trying: SELECT * INTO OUTFILE 'C:\Users\Jim Smith\Desktop\zyzyz.csv' ...
0
votes
0answers
10 views

how to store binary (not blob)

I have a "collection" of boolean values that I need to store, in a database. It's a total of approx. 200 booleans that fit into ~13 groups. Each user that would be registered has his own set of ...
0
votes
1answer
7 views

Mysql 1 way replication master lost data replication still has

Had a master mysql on (windows) improper shutdown. Many records could not be recovered and investigation found slave had records master did not have. Did not restore over master - left as was. As if ...
0
votes
0answers
30 views

MySQL Dynamic columns duplicate value workaround

As the title suggests i have the following code which transposes certain row values into field names, however the problem im having is that the values "closed" & "reopened" may appear in several ...
0
votes
0answers
25 views

Increasing limit by 1 results increases execution time from less than a second to infinite

Using a simple query like: select * from <table> limit 5; will return results in a fraction of a second, which is expected. I can increase the limit up to 49, and the results will still be ...
0
votes
1answer
17 views

How to get something written to MySQL error log without restart

I want to cause the MySQL 5.5 error log to be written to at will without a service stop or start. Note that I do not care what gets written to the error log, as long as something gets written. I ...
0
votes
1answer
20 views

How to report durations of 100+ queries in MySQL?

I have list of 142 SQL queries in an .sql file. At the beginning of this file I put SET profiling = 1;. At the end SHOW PROFILES;. I execute it with: mysql -u root live_copy < ...
0
votes
0answers
22 views

MySQL Query Causing Massive Slowdown Over Time

I've been experiencing massive slowdown on my server after launching a new website. Using MONyog I can see that after an apache/mysql restart, the queries/second starts out nice and low for around 10 ...
0
votes
1answer
18 views

What should be the structure for a person-to-person store database

I am going to create a person-to-person store with Laravel, a website whose users can share and sell their product or their mobile, TV, Computer, Shoes, Shirts, Books ,Cars, Bags, Animal etc. I am ...
6
votes
2answers
49 views

Prevent MySql from mangling queries by casting string to int

On one of my tables I have an auto incrementing ID field. It also has another field (richter_code) with a unique constraint but, over the seasons, this field can change - that's why I'm not using it ...
0
votes
0answers
8 views

Performance/Maintenance Implications of a Write Heavy Table In An Otherwise Normal “CRUD” Database

We have a small-ish (<100,000 rows) MySQL database (on Amazon RDS). Its supporting a fairly simple read heavy CRUD web application. Currently we're building out a web analytics system. It will ...
1
vote
1answer
25 views

Database query: join tags with exact matches or at least one

Given I have a query like: SELECT count(mm.id) AS c FROM merchant_mobile mm INNER JOIN mobile m ON mm.mobile_id = m.id LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id) INNER ...
0
votes
0answers
59 views

MySql: Get count of incremented items by multiple conditions

Here's the create and insert script, it's a calls record data table. This is a glimpse of it: | call_id | customer | company | call_start | ...
-1
votes
1answer
36 views

converting mysql datebase

I have a question about converting mysql databases, I doing now new site, and want to use my old database, which is in latin_swedish_1 colacolation I want to convert this old database into ...