MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
35 views

Localization: One table or multiple tables? (MySQL)

I need to be able to store string messages in various languages. I guess I need a column for each language string. For example my columns could be: ID | message_EN | message_DE | message_FR Now, I ...
-1
votes
1answer
38 views

Statements in State “Copying to tmp table” disrupt performance [on hold]

On a MySQL database (version 5.1.73) a Magento-Shop executes a statement (A) that uses distinct, see http://pastebin.com/A2sy45nF and therefore is "Using temporary" according to "explain". ...
0
votes
1answer
31 views

Multiple left join hangs server

i've got problem with left join query. It connect's 3 tables and calculate sales value for categories. Query: SELECT categories.name, categories.id, ...
0
votes
2answers
36 views

How can I remove only consecutive duplicate fields when the id is not consecutive in MySQL

In this table, I have a price change history. The prices are checked regularly and as you can see, sometimes the price does not change. How can I take a specific sku and delete all cases where a ...
0
votes
0answers
43 views

insert data into mysql from mssql with arabic unicode

I insert data into MSSQL with arabic unicode. I want to insert this field into MYSQL but it insert empty. In MYSQL , I choose utf8-bin , I use php for this action, I declare name , character unicode. ...
0
votes
1answer
40 views

Cannot drop databse

I am unable to either drop database, now create the database. Following is the output. How can I fix this? mysql> show databases; +--------------------+ | Database | ...
0
votes
1answer
56 views

Optimize this query

SELECT `permission_tbl`.`service_id` AS `service_id`, `permission_tbl`.`shop_id` AS `shop_id`, `permission_tbl`.`easy_id` AS `easy_id`, `permission_tbl`.`permission_type` AS ...
-1
votes
1answer
54 views

Using Select statement twice for same column of table for MySQL

I'm supposed to write a query for a database that is a little tricky for me. It has an EMPLOYEE table that has these attributes(columns): Emp_Id,Name,Age, Dept_ID, Salary It also has a DEPARTMENT ...
0
votes
1answer
81 views

SQL execution 2 seconds with LIMIT parameter, without only 0.2s why?

This is my query and I need to return only 12 rows, but it takes 2 seconds with parameter LIMIT 12 while it takes only <0.2 to process all ~600 results (without LIMIT 12 or with LIMIT 1200 for ...
0
votes
1answer
35 views

How to design a query that gives count of different parameters based on different condition from a single table

I have a table books containing columns as bookid, issue_date,return_date. Once a book is issued from library an issue_date corresponding to it is made into the table while return_date can have valid ...
0
votes
1answer
27 views

SQL: return values as column table

I am running this query to get all returns for a list of tickers: select return1 from STOCK_DATA where TDATE between 20130101 and 20131231 and TICKER in (SELECT DISTINCT TICKER FROM STOCK_DATA) ...
2
votes
1answer
45 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
0
votes
0answers
55 views

MySQL JOIN vs LIKE - faster selects? [migrated]

Okay, so first of all let me tell a little about what I'm trying to do. Basically, during my studies I wrote a little webservice in PHP that calculates how similar movies are to each other based on ...
0
votes
1answer
37 views

Mysql pagination without offset

Let's assume I have got a table with these data: I want to order it by createdAt column DESC, and id column DESC select id, createdAt from post order by createdAt desc, id desc Now it looks like ...
0
votes
1answer
36 views

where in (sub query) vs join

just want to ask about how to optimize my query. because i feel efficiency using join instead of "where in" sub query can someone enplane the deference SELECT C.Customer_id, C.tran_id, ...
0
votes
0answers
28 views

MySQL: How to get all values in “TICKER” column`? [migrated]

I never used SQL before, I want to get all values in "TICKER" from this database: The first statement select TDATE, price, return1 from STOCK_DATA where TDATE < 20131231 and TDATE > ...
-1
votes
0answers
16 views

Template for MySQL database upgrade script

What is the equivalent of this Microsoft SQL Server upgrade script template for MySQL? I want to upgrade my database version in repeatable transactions in sequential scripts as I develop an ...
0
votes
0answers
24 views

Advice on table structure change

I am in the process of converting an Access application that stores and reports evaluations to being web-based using php and mysql. I have converted the structure and data over from Access and I have ...
0
votes
0answers
37 views

How to notice data lost in innodb engine

I have a web-application installed in some desktop pc, and I experienced some troubles with mysql innodb. In case of hardware fault (disk failure) or just power fault I lost many data in my database ...
0
votes
0answers
9 views

this code is not deleting a row whose id comes from form? but running fine without any error? [migrated]

#!/usr/bin/perl use strict; my $query = new CGI; print $query->header( "Content-type: text/html; charset=utf-8" ); print <<END_HERE; <html> <head> ...
-1
votes
1answer
31 views

Update table using SELECT in WHERE clause

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON t2.t1id = t1.id LEFT JOIN table3 as t3 ON t3.t1id = t1.ID SET t1.status = 4, t2.status = 2, t3.status = 2, WHERE t2.status = 1 AND t3.StatusID = 1 AND ...
-1
votes
0answers
19 views

Does database compress data?

I have a file with 5000 rows and 10MB in size. Winrar is able to compress it to 1MB. If I store in a database, will it take 1MB or 10MB? If I retrieve it from a remote computer via c#, will it be 1MB ...
0
votes
1answer
16 views

Triggering a shell script from MySQL without any 3rd party libraries

Is it possible to call a bash script file in linux using a trigger in MySQL? I've been searching for the answer online and I ran into this articles: ...
1
vote
0answers
45 views

Handling SQL updates for production deploys

I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment. I run a pretty ...
0
votes
0answers
33 views

How alter on infobright (BRIGHTHOUSE storage engine) actually works

I have been using infobright-3.4.1 (5.1.40) for data warehousing. I know it is outdated version but there no plan to upgrade the version currently. Even though infobright is build on mysql, the way it ...
0
votes
0answers
9 views

Enable general_log in Mysql 5.0.96

I want to enable general_log in MySql 5.0.96. I have set log variable but it isn't working. [mysqld] log=/var/log/mysql.log and restart mysql with following command /etc/init.d/mysql restart But ...
0
votes
1answer
31 views

Federated table of two LEFT JOIN tables not working

For some reasons I am getting the following error when executing the below query: (What I am trying to achieve is to sync and merge two left-joined table to into one You have an error in your SQL ...
0
votes
0answers
18 views

mysql trigger help

I need to create a MySQL trigger. I don't know very well MySQL syntax but I've written something, maybe you would understand what I need, I need to insert the difference from 2 rows into a new row, ...
0
votes
1answer
31 views

(MySQL) Need an “in-place” REPLACE for adjusting historical data

I have a table with around 6,000,000 records. It is backed up every few hours. Recently a script ran that cascade updated about 2,000,000 of the older records to bad values. I have extracted those ...
1
vote
1answer
24 views

Find MySQL Patch Level

What command do you use to find the patch level of MySQL? I am currently on 5.1.73. I need to find out what patches have been applied to this database. Thanks...
0
votes
0answers
42 views

MySQL Master-Master-Slave failover

I have setup a Master-Master configuration in MySQL with one additional slave, like this: Master1 <-> Master2 <- Slave Master1 is the write master used by my application, Master2 is used ...
0
votes
1answer
58 views

Simple query is slow on 4M-rows table

I have in production a MySQL table pageviews with 4M rows that records page views of users on posts. I need to know which posts a specific user have read, but this request takes up to 15 seconds to ...
-1
votes
1answer
33 views

List two columns - MYSQL [closed]

i have a table like this: cp1 | cp2 ----------- A | 5 A | 1 A | 1 B | 3 C | 4 and i want to create a query like this: cp1 | cp2 ----------- A | 5 A | 1 ...
0
votes
0answers
22 views

Checking for replication options

I have a master/master setup with one DB in each. DB name and tables in one server are different from the other, and few 'user' tables are being replicated with the option replicate-do-table. (It's ...
0
votes
1answer
56 views

MySQL Performances Issues

I'm in debate with my database admin. He claims that my queries I'm sending through PHP are the problem and he is stating several things I believe are completely false. To begin I have a site ...
0
votes
0answers
11 views

How can I see all the incoming queries in real time to my MariaDB 10 server on windows?

I just want to see the queries coming in to MariaDB (very similar to mysql) in real time to help debug my application. What windows frontends are there that do this sort of thing? Thanks
1
vote
1answer
32 views

Slow execution of a subquery with 0 matches

In MySQL, I have two basic tables - Posts and Followers: CREATE TABLE Posts ( id int(11) NOT NULL AUTO_INCREMENT, posted int(11) NOT NULL, body varchar(512) NOT NULL, authorId int(11) NOT ...
0
votes
1answer
35 views

Should I use data type SERIAL for table id columns?

I found out that there is a mysql datatype called SERIAL, it appears to be designed for use as an id column. However some of my peers don't think that the real data type BIGINT implied by SERIAL is ...
0
votes
1answer
111 views

MySql replication from 5.1 master to 5.6 slave keep crashing

I recently updated one of the replication slaves to mysql 5.6. After importing a recent dump from the master, I run mysql_upgrade with no errors. the database deamon started correctly with no ...
1
vote
1answer
41 views

MAX (id) function with MySQli

How can I use the following statement to return only the record with the maximum PersonID from the table? /* Create the prepared statement */ if ($stmt = $mysqli->prepare("SELECT Max(PersonID) AS ...
1
vote
1answer
38 views

MySQL - OPTIMIZE TABLE for partitions

I would like to perform OPTIMIZE TABLE opearation , this table is a partitioned table. Which method can be better in this scenario: OPTIMIZE TABLE tblname; or ALTER TABLE tblname ...
2
votes
1answer
54 views

How mysql (innodb) handles auto increment column

I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can ...
0
votes
1answer
30 views

MySQL Loop Through Each Row

I have two tables, division & template, in a MySQL database. division - DivisionID - DivisionName template - TemplateID - TemplateName Here is an example of my current data ...
0
votes
1answer
70 views

Does the order of a mysql multi-column MySQL index matter - if you're always using all columns in the query?

I'm aware that when you define a MySQL index that the order of the fields matters if the query is only using part of the index, as described here. But if your query is always going to use all columns ...
0
votes
1answer
42 views

What does SELECT ~0; do?

I've recently stumbled into an interesting Mysql query: SELECT ~0; results: 18446744073709551615 Which is the max value of a bigint unsigned. What does the tilde operator do in this case?
0
votes
0answers
26 views

WHERE in CASE in mysql

$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))." FROM (select CASE when r.agent_id=24 THEN r.Unit ELSE '--' END AS MyUnit, CASE when r.agent_id=24 THEN ...
0
votes
2answers
102 views

Do I need to reindex mysql table after bulk delete?

I have a table in MySQL with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
0
votes
2answers
63 views

Do I need to reindex my table in mysql after delete?

I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
0
votes
0answers
25 views

Data on the slave becomes inconsistent after couple of days

We are planning on upgrading our production MySQL server from MySQL 5.0.77 to Percona 5.6.19-67.0. Since our downtime window is small, we setup a slave of the production master on a new host and ...
0
votes
1answer
47 views

Copy Data To Different Server using SELECT * INTO?

I'm not a DBA by any means. I'm just knowledgeable enough with the MySQL command line to be capable half the time and dangerous the rest. Today I was working on exporting data from a one DB into ...