MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
284 views

query optimization delete nested query [closed]

Can the following query be optimized ? DELETE FROM COLOR_TAGS WHERE COLOR_PROJECTID = $id AND NoteID IN( SELECT ID FROM note_note WHERE BibID=$bid AND isdel=0);
6
votes
1answer
13k views

Why is LIKE more than 4x faster than MATCH…AGAINST on a FULLTEXT index in MySQL?

I'm not getting this. I've got a table with these indexes PRIMARY post_id INDEX topic_id FULLTEXT post_text Table has (only) 346 000 rows. I am trying to perform 2 queries. SELECT ...
8
votes
1answer
2k views

MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
5
votes
2answers
16k views

innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster. We have 125G main memory with 24G InnoDB buffer pool ...
3
votes
2answers
5k views

MySQL Workbench copies Schema, but not data, from SQL Server 2008

I need to copy/migrate a database from MSSQL (SQL Server 2008) into MySQL. I am using the MySQL Workbench kit, and work through the process of the Migration. Each respective DB user has full access, ...
7
votes
1answer
5k views

When to switch from MyISAM to InnoDB?

We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM. My original plan was to upgrade ...
4
votes
2answers
804 views

How to resolve the master server shut down/unavailability in mysql with master - slave replication

Let us suppose that we have a master/slave setup and the application is running fine for the time being. All of a sudden the master server suddenly shuts down due to some problem. Now how can I ...
1
vote
4answers
4k views

Should I use the Infobright database or is there a better aggregation solution?

We are a MySQL front-end shop and have a system currently logging event data in a highly structured format. Think apache traffic log. We need to be able to aggregate counts of this data (cube) vis a ...
0
votes
1answer
2k views

MySQL Innodb: What is the difference between running 'mysqlcheck --optimize --all-database' compared to running 'optimize table' in the mysql client?

Without having to script an 'optimize table' query to do all the databases/tables, we would like to run mysqlcheck --optimize --all-database instead. What are the drawbacks of doing this through ...
1
vote
3answers
570 views

query optimization for update nested query IN(SELECT

Can we optimize this? UPDATE TABLE_NOTE SET COL_NOTE_DISPLAY='BLOCK' WHERE COL_NOTE_BIBID = $projectID AND $COL_NOTE_ID IN ( SELECT COL_PILENOTEREL_NOTE_ID FROM TABLE_PILENOTEREL ...
1
vote
1answer
861 views

Removing/overwriting sensitive data located in already deleted records in ibdata files

As far as I know, using InnoDB tables with innodb_file_per_table = off does not prevent deleted data from being visible in the ibdata1 file. I know this is by-design, and that I could switch the ...
3
votes
3answers
340 views

Database design for changing number of fields

Google Contacts lets you to enter more than one phone number, or email address for a contact. You can add as many as you want. I don't know if there is a limit, other than practical list size. To ...
3
votes
3answers
419 views

An “unlimited” data model

I need to store an unknown amount of fields. Some are numbers (like IDs, zip codes and times), some are short strings (like names and states), and some are longer strings (such as descriptions). All ...
2
votes
1answer
4k views

How to Create Snapshot Backups in MySQL

I've seen a lot of people talking about snapshots backup, even I'm reading a book about mysql but nobody explain the code how to make a snapshots backup. This is what the book says: flush tables ...
5
votes
2answers
3k views

Running multiple instances on the same host

I need to set up multiple MySQL instances on the same host for one of our customers. As far as I see I could go with: mysqld_multi MySQL Sandbox This should be a long term solution for an ...
3
votes
1answer
6k views

Get two weeks of data but group by a period of 7 days

I am looking to run a query that collects the number of results over the last 14 days, this is fairly easy and achieved like so: SELECT COUNT(id) FROM table_name What I'd like to do is then group ...
4
votes
2answers
880 views

Sorting the Table and getting the position

I am able to sort a table using the following code:SELECT * FROM Persons ORDER BY LastName But I wanted to get the ranking/position of a person using that order. For example when I sort Person table ...
5
votes
1answer
2k views

Mysql sub query returns more than 1 row

SELECT * FROM wp_posts WHERE ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
7
votes
2answers
2k views

MySQL : perform a big data migration between tables

I want to migrate data between 2 InnoDB tables. Currently I'm running this query: INSERT INTO table_a SELECT * FROM table_b; If the dataset grows, what's the best way to avoid CPU overload? ...
0
votes
2answers
3k views

Oracle database link to MySQL database on RedHat Linux

I have an Oracle 11g database on a RedHat server and also have MySQL database on another RedHat server. I want to create a link to the MySQL server from the Oracle which allows me to execute queries ...
0
votes
2answers
100 views

MySQL still holding memory after I shut it down?

I used this command to shutdown MySQL, however it seems the amount of free memory on my box has not changed? That seems to odd. /usr/local/webserver/mysql/bin/mysqladmin -u ${mysql_username} ...
11
votes
3answers
1k views

Where is the INNODB Performance Leak?

I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me. The Situation I am working on a system which handles a lot ...
0
votes
1answer
307 views

What would be possible reasons for a DeadLock to occur in Mysql Database?

If some one has a Table structure some thing like this, CREATE TABLE IF NOT EXISTS `domain_source` (`nid` int(11) NOT NULL DEFAULT '0', `domain_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`nid`) ) ...
1
vote
2answers
1k views

SQL Partioning on date, how does it work for future records?

I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have ...
2
votes
1answer
1k views

MySQL query not using an index when table contains many records

I have a table: CREATE TABLE `p` ( `id` BIGINT(20) unsigned NOT NULL, `rtime` DATETIME NOT NULL, `d` INT(10) NOT NULL, `n` INT(10) NOT NULL, PRIMARY KEY ...
2
votes
1answer
960 views

MySQL: AVG of defined variable per month

I need help with the following code: SELECT DISTINCT userid , count( userid ) as login_count FROM ( SELECT DISTINCT userid , ...
4
votes
1answer
1k views

MySQL Import CSV with Multiple Field Separators

I'm trying to import this CSV file into MySQL that appears to be optionally enclosed by more than one character. Unfortunately, MySQL only supports one character as a field separator. I am stuck ...
4
votes
1answer
656 views

MySQL: Delete a record from all tables in one shot

I have a MySQL database, with one table that has a primary key, f_id that is referenced in multiple table as a foreign key. I want to delete all records where f_id is a specific value from all ...
4
votes
2answers
334 views

How to speed up this simple mysql query?

The query is simple: SELECT TB.ID, TB.Latitude, TB.Longitude, ...
8
votes
3answers
7k views

How to Best Implement nearest neighbour search in mysql?

So, in short, What should be the data type of latitude and longitude? What SQL command I should call to get the first 100 nearest restaurants for example? Detail: I have 100k biz record each with ...
2
votes
1answer
94 views

Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0

I didn't know if this is possible using just a query or if I need some procedural logic on this one. I have this table: CREATE TABLE `default_messages_users` ( `id` int(11) NOT NULL ...
1
vote
1answer
1k views

How should I update ID field values for all records based on previous values?

I need a SQL query that can update ID fields for all records in a table from their previous values to a new value based on that previous value. For example, if the ID is 1, I want to change it to ...
1
vote
1answer
288 views

MySQL suddenly running very very slowly

I run a PHP/MySQL website on a fairly large server with 16GB RAM and 3 vCPUs. Everything was flying along until this morning when I noticed MySQL down. I tried to restart MySQL and first got the ...
2
votes
1answer
4k views

How can I limit MySQL connections?

Last week, our MySQL database got overwhelmed with a lot of connections doing INSERT / SELECT queries. I have talked to the developers who say it will take weeks to fix this issue. In the mean ...
5
votes
1answer
2k views

MySQL dump restore - how to retain comments

So I have that complex MySQL dump as a backup. Unfortunately, that backup is complex and we use evil triggers. Some of our evil triggers had a comment lines starting with the # character. When I ...
4
votes
4answers
6k views

Is MySql locking table during backup and how to avoid?

I have a scheduled backup that runs daily in MySQL and I have noticed that during the backup a lot of queries that query a frequently used table seem to take forever to complete. This causes the ...
2
votes
1answer
282 views

MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

I'm trying to estimate my db traffic not including backups (mysql 5.1.41). I use show global status; to get the parameters bytes_sent and bytes_received. Questions Do these numbers include ...
2
votes
3answers
2k views

How to detect a running mysqldump?

Here are the things I can't change: We got a mySQL myISAM replication running a master and some slaves. One slave is being used for nightly backups using mysqldump. The backups is using heavy locks ...
1
vote
2answers
495 views

Managing features for a real estate agency [duplicate]

I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table. The data ...
0
votes
1answer
583 views

MySQL table with multiple values in one field

I'm building a database for a real estate company. The real estate properties are managed by sales people using a CodeIgniter website. How should I proceed with the database design for this table? ...
-1
votes
1answer
534 views

Very slow MySQL query. Index problem?

I have a query like this: SELECT BE.NAME as NAME, BE.PREVIEW_TEXT as PREVIEW_TEXT, BE.ID as ID, FPV1.VALUE as PROPERTY_COUNTRY_VALUE, FPV1.ID as PROPERTY_COUNTRY_VALUE_ID, ...
1
vote
1answer
5k views

Connecting remote MySQL database to local MySQL database?

I want to write PHP code to be embedded in a Drupal7 module. I want to call a procedure which can copy newly generated data in the local MySQL database to a remote MySQL database. When data is ...
2
votes
2answers
5k views

Remove the last character from Variable String

I have a few variable data in the column "email". For example: roy2087@gmail.com123 sg123@yahoo.compori How do I get rid of the characters after .com? I used Replace(email,'.com%',gmail.com), ...
1
vote
2answers
102 views

Basic PARTITION question in MySQL

Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance. Would I need to change the applications select statements ...
2
votes
1answer
617 views

InnoDB not enabled

I have a mysql database that when I run SHOW ENGINES; innodb does not show up as an engine. This seems strange as I have an identical server setup where innodb is enabled and in use. I have checked ...
2
votes
1answer
714 views

MySQL wont start after modifying /etc/my.cnf

I have Linux Red Hat 5, with Zend Server and MySQL Server installed. I am trying to change the default MySQL port to 512. However, after making this change, MySQL will no longer startup. I get the ...
1
vote
1answer
469 views

MySQL to add to cart 1-4 Socket Server

When I check for price tags for MySQL, I see a clause called "1-4 socket server" or "5+ Socket Server".. what does this mean? "Socket" in generic term means Inter Communication Protocol. For MySQL, ...
4
votes
2answers
8k views

Why is my MySQL dump file and my phpMyadmin export file different sizes?

I am looking to create a cron job to backup my MySQL database. I am using this command: $ mysqldump --user="User" --password="Password" -A --single-transaction > /home/user/Export-`date ...
4
votes
1answer
114 views

Is it possible to encapsulate a table in MySQL?

I am new to Databases. I want the following: UPDATEs in a database to happen only through a stored procedure The user does not have GRANT UPDATE The user has GRANT EXECUTE ON PROCEDURE I tried it ...
4
votes
2answers
9k views

How do I get the current and next greater value in one select?

I have a InnoDB table 'idtimes' (MySQL 5.0.22-log) with columns `id` int(11) NOT NULL, `time` int(20) NOT NULL, [...] with a compound unique key UNIQUE KEY `id_time` (`id`,`time`) so there can ...