MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
2answers
1k views

How would I run DELETE queries on a Master Without Executing Same DELETEs on a Slave?

If I want to run a query that will only execute on the master mysql server and not the slave. assuming we have an environment with replication through query logs. Could I and how? The idea is to have ...
4
votes
1answer
1k views

What's the fastest way to replicate a MySQL database from one machine to another?

What's the fastest way to replicate a MySQL database from one machine to another? The two machines are connected via LAN. How to do it without mysqldump? If using mysqldump, is the fastest way $ ...
1
vote
1answer
3k views

MySQL - SELECT INTO OUTFILE, with a UNION

Is it possible to issue multiple select queries whose resultsets are combined via union, into the same outfile? What I am trying to do, but not working: (SELECT email FROM table WHERE createdate ...
3
votes
2answers
839 views

MySQL insists on trying to use InnoDB even when told not to, please help

We're trying out MySQL 5.5, and have installed it on one of our slave servers as a test run. Our main server is 5.0, and we have zero optimization here for InnoDB, so I want everything to just be ...
2
votes
2answers
209 views

mySQL Relational Tables

Say I have 3 tables Products, Orders and Orders Product Products Table ID | StockNum | Description | Price 4 | 452SB | Black | 45.65 5 | 752SA | Green | 17.12 Orders Table ID ...
1
vote
1answer
2k views

MySql command line cyrillic Iinsert result is corrupted

Have putty connection. Putty config : window-->translation-->remote character set-->utf-8 Launch command line MySQL client. Perform insert with cyrillic like: CREATE TABLE `category` ( `catName` ...
2
votes
1answer
1k views

How to speed up mysql query?

I have query : SELECT `school`.`ARCO_name`, `student-`.`ClassSize_7`, `student-`.`ClassSize_8`, `degree_o`.`degree_code`, `accredit`.`full_faculty_3`, ...
0
votes
0answers
22 views

A spatial query return no result when obviously there is result [duplicate]

Possible Duplicate: Less restrictive query return less result due to simple removing one additional constraint SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, ...
2
votes
0answers
1k views

SQL query optimization in Prestashop e-Commerce solution

I'm working on a layered navigation module within Prestashop e-Commerce solution. I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
0
votes
2answers
4k views

How to manage a huge database?

We are building a service witch require a huge database. We are actually planning on getting more than 100 millions rows in our biggest table. So, what are the best options to store all this data in ...
0
votes
1answer
510 views

Can I rollback/abort a transaction if no rows were inserted?

I have a transaction in which I want to do something like this: START TRANSACTION; INSERT INTO table1 (...) SELECT ... FROM table 2; ... if select count(*) from table1 returns 0 abort/rollback ... ...
3
votes
1answer
2k views

Any disadvantages of “SQL_BUFFER_RESULT” for MySQL SELECT?

I've been researching the usage of SQL_BUFFER_RESULT. Mostly it is referred to as an aid to reduce table lock issues. It seems to be a good option to use. However I cannot seem to find any ...
0
votes
1answer
689 views

Is it possible to resume an interupted mysql import from a backup file?

I created a backup file of my database with phpMyAdmin and checked the following data dump option: include column names in every INSERT statement Example: INSERT INTO tbl_name ...
1
vote
1answer
294 views

Database inserts/updates slow only in Production

We have a int/test/staging/production Mysql database setup. A Java application(using Hibernate) that periodically generates up to 200,000 updates/inserts (mainly updates by 20 to 1) completes in 10 ...
0
votes
1answer
149 views

Backup and trimming data idea

Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows. We would like to maintain ...
2
votes
2answers
155 views

How can I delete repeated rows?

I have a system that gathers RSS from many sites including UTF-8 based sites (non-english sites). I have tried to avoid repeated rows but I have repeats in my table. RSS_items (id(auto_increment) ...
3
votes
2answers
4k views

mysql workbench *reverse engineer* is not connecting tables graphically. How to fix?

I thought that previously when I reverse engineered databases, mySql Workbench figured out the relationships from the id names and connected the tables leading to an awesome ERD, even when the foreign ...
1
vote
1answer
331 views

`SELECT FOR UPDATE` in master-master mysql setup

I am benchmarking a database schema with a dozen workers processes and was wondering what a SELECT FOR UPDATE does in a master-master configuration. For the moment it seems to work, but I am either ...
1
vote
1answer
323 views

What storage engine should I use for this MySQL table?

I have a table where I store all the urls from my website, and I update them from time to time when a visitor enters. The primary key is the url itself varchar(255). I have set it to use InnoDB but ...
1
vote
1answer
1k views

PDO count(*) does not work with LIMIT and OFFSET

here's my code it works normally, but this time i want to also count the records it gives. at the point of inserting COUNT(*) it will give NULL value to counted rows $query_list_records = ...
4
votes
2answers
15k views

Can I execute a prepared statement inside a MySQL SELECT?

I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all ...
1
vote
1answer
74 views

MySQL Replication - replication issues; procedures registered on master not giving same result on slave in MySQL 5.5.20

I am not able to replicate procedures registered in the Master to the Slave in MySQL 5.5.20 The Procedure is registered on the Master and can be seen on Slave. However, results fetched using the same ...
2
votes
1answer
134 views

MySQL REPLACE appears to corrupt data

For reference, this issue was posted on StackOverflow.com before coming here. In MySQL I am running multiple REPLACE queries to update hardcoded URLs in a database. Queries look like this: UPDATE ...
6
votes
2answers
424 views

Less restrictive query return less result due to simple removing one additional constraint

Look at this query SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance FROM ...
3
votes
1answer
408 views

How does R-Tree outperform B-Tree for simple check if a point is within a rectangle

For things like find nearest neighbors, I can undertand that R=Tree can outperform B-Tree. R-Tree can kick out more obviously false points. However, for simple check of a point in a rectangle, it's ...
1
vote
0answers
107 views

Why do we need myisam spatial extension anyway?

If just to determine whether a point is within a rectangle, What is wrong with this query? EXPLAIN SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude FROM TableBusiness AS TB WHERE ...
0
votes
1answer
306 views

Why number of matches in fulltext search get less when we put less strings?

SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('resta*' IN NATURAL LANGUAGE MODE) show 5 results SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('restaurant*' ...
2
votes
4answers
1k views

best mysql field type for temperatures

I'm realising that this is the first time I've stored temperatures in a MySQL DB. What do think the best mysql field type for temperatures (c) is? Decimial (3,3) ?
1
vote
1answer
200 views

What is the SQL command to get 20 closest points from some center location

There are 1.6 million rows. Must not compute distance for all rows. Spatial index is available This is the table structure. The name of the table is businessauxiliary. So yes I used MyIsam, added ...
0
votes
1answer
296 views

MySQL Partition based on month-year

Is there a way that I can create a month-year partition on the below table? Without having to define all the permuations of month/year combos ? CREATE TABLE visitor_visits ( id int(10) UNSIGNED ...
11
votes
1answer
6k views

MySQL transaction size - how big is too big?

I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction. There are many aspects, and the imports may yield anywhere between ...
6
votes
4answers
3k views

Recommended MySQL statistics for production environment

Other than usual system stats (i/o, ram usage, cpu, load, etc) I am currently collecting questions, qps, running queries and buffer pool hit. I find qps pretty useless since our production servers ...
1
vote
1answer
847 views

Delete by primary key takes too long

What would make a query that deletes a row using the primary key of the table .. take too long ? This is a table with about 1.4M rows that gets .. 70%/30% read/write . I don't know where to look. ...
17
votes
3answers
39k views

Safest way to perform mysqldump on a live system with active reads and writes?

I'm not sure if this is true but I remember reading if you run the following command in linux mysqldump -u username -p database_name > backup_db.sql while reads and writes are being made to a ...
3
votes
1answer
886 views

Best Approach for Mirroring Data from SQL Server to MySQL

I need help in regards to replicating data from a third party point-of-sale system with a SQL Server backend to a MySQL DB. Specifically, can anyone suggest a best way to mirror data from SQL ...
3
votes
1answer
4k views

Getting unique names when the IDs are different (DISTINCT ?)

I've got this table: id | name 1 | John 2 | John 3 | Peter 4 | Mike 5 | Mike And I want to get the unique names, getting the latest one in the list. I could go for: SELECT DISTINCT name ...
3
votes
1answer
1k views

MYSQL Slow SELECT with multiple joins and ORDER BY

I have this query SELECT r0_.id AS id0, r0_.deleted_at AS deleted_at1, r0_.updated_at AS updated_at2, r0_.created_at AS created_at3, r0_.is_vehicle_offer AS is_vehicle_offer4, ...
2
votes
2answers
4k views

MySQL - automatically update all references to specific table to use on delete cascade?

I have a specific table for one of my software packages on a MySQL 5.1 box which I need to drop a few hundred entries. These are user names with names in uppercase, the application is case specific ...
3
votes
4answers
2k views

One table with many rows or many tables with a few rows

I'm having a question rounding my mind. It's better to create one table and store a lot of rows there or create various tables and have a few rows on them? What's the performance and the I/O saved at ...
1
vote
1answer
113 views

How can I reset the index of the rows?

I would like to select and insert data from an old table to the new table: CREATE TABLE test2 LIKE test1 INSERT INTO test2 SELECT timestamp, datenum FROM test1 WHERE datenum>=23 and ...
1
vote
1answer
656 views

It is possible to export a MySQL database without BLOB fields?

If I have a MySQL database with some tables where one have a BLOB column, It is possible to export the whole database excluding the BLOB fields? If not, could you suggest a solution to work-around it? ...
2
votes
1answer
2k views

Why does MySQL only sometimes use my index for a range query?

I have a table of IP address ranges and their country, here is the structure: CREATE TABLE `geoIP` ( `startBlock` int(11) unsigned NOT NULL DEFAULT '0', `endBlock` int(11) unsigned NOT NULL ...
0
votes
3answers
3k views

Will using bigint vs mediumint have a performance impact?

I'm considering using mediumint or bigint for an ID column. I prefer to use bigint, because that way I'm very sure that the huge numbers that the client sometimes uses are stored just fine. However, I ...
3
votes
3answers
622 views

This query is choking the server. How can I improve it?

Following query is hogging a lot of resources, as much as that the queries are getting locked down & CPU usage goes through the roof! The MySQL Processes list shows that as the number of requests ...
2
votes
3answers
635 views

Tuning Question

Every so often our box is HAMMERED to a crawl %Wait goes to 40 or 50% and connections grows to 300 or so. i think its due to a query running, but i wanted to see if any of the settigns on the mysql ...
1
vote
2answers
2k views

MySQL Cluster On Cloud vs MySQL Cluster On Dedicated Cluster

I saw this interesting article : http://mikaelronstrom.blogspot.com/2012/02/105bn-qpm-using-mysql-cluster-72.html and it made me get interested in MySQL Cluster. After little chat with hosting ...
1
vote
1answer
67 views

How to order by 5, 501, 50101, 6, 601, 60101, etc?

I've got a list of ledger account numbers. These numbers go like this: 5 (main account, #5) 501 (sub account of main account 5, #01) 50101 (sub account of this account, #01) etc. So if I have a ...
2
votes
2answers
1k views

Innodb: Operating system error number 1117 in a file operation

i installed apache and mysql 5.5 on windows 7 64 bit. it worked but today mysql can not start. when i see at error log i find this 120616 16:35:47 [Note] Plugin 'FEDERATED' is disabled. 120616 ...
1
vote
2answers
93 views

Trying to optimize a MySQL query that has sub selects

I have the following sql SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate, (SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as ...
3
votes
1answer
1k views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...