MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
359 views

Retrieving 10 records from multiple tables

I have two tables where I store post data. What I want to do is to get 10 records from those two tables. Table 1 - all posts, Table 2 posts that user read. There are some scenarios; User has never ...
1
vote
4answers
655 views

MySql Order by isnull() Performance Problem

my sql below is use for listing stock added from 10 days ago. Order by isnull(Price) is use so that stock without any price yet will still being listed. AddDate and Price has an index. SELECT Id, ...
1
vote
1answer
1k views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...
1
vote
2answers
436 views

Is it okay to rename the MySQL data files (FRM) manually?

Like everyone, I realized the case sensitivity issues in MySQL of late. I wanted to convert my upper case table names to lower case using this solution but I forgot to do Step 1 and completed the ...
1
vote
1answer
1k views

Ubnutu/MySQL - Error “Unable to locate package innotop”

I am using Ubuntu 12.04.2 LTS, with MySQL 5.5.29. Im trying to install innotop for database monitoring, but getting an error "Unable to locate package innotop" root@server1:~# apt-get install ...
1
vote
2answers
361 views

Challenge. Split database 500 records in two smaller datasets, randomly

I have a database with 500 records. I want to split these records to 75% and 25% *randomly*in order to use the different datasets for training and testing to machine learning algorithms. Does anyone ...
1
vote
2answers
5k views

Is there a way to stop MySQL Replication on the master?

I want the master to stop replicating data to the slave. I know I can do that on the slave with STOP SLAVE;, but I wonder if there is a way to do it in the master. One possible solution might be to ...
1
vote
2answers
589 views

MySQL: CAST + SUBSTR vs. FLOOR + MOD

I have a field in a mysql database where dates are stored as BIGINT like YYYYMMDDHHMMSS (PHP: date('YmdHis')). I'm building a query, where I check the amount of rows per hour and have tried two ...
1
vote
2answers
2k views

Stress test MySQL with queries captured with general log in MySQL

Is there any tool available which can do stress testing using the log file created by MySQL general log? After a lot of search on google I found few stress testing tools which only use some benchmarks ...
1
vote
1answer
825 views

Scheduled event did not start when expected

I have created one event schedule which looks like this: mysql> show create event event1 -> ; ...
1
vote
2answers
1k views

How can I migrate a sybase database to mysql? [closed]

I've been tasked with migrating a fairly complex legacy Sybase database into a MySQL database and I have no idea where to start. I can't use any off the shelf software, so I'm going to have to write ...
1
vote
1answer
1k views

SQL Server to MySQL migration - how can I remove UCS-2 surrogate pairs in SQL Server?

I have been tasked with migrating a SQL Server 2005 database to MySQL 5.6 (these are both database servers runnig locally) and would really appreciate some help. -------Edit - Answered------- This ...
1
vote
3answers
461 views

Complex query with multiple normalized fields

We have a fairly simple table structure, but with a LOT of fields per table (talking 40+). This data is initially produced in plain-text, user-readable tables, but then it is translated into ...
1
vote
3answers
2k views

how to dump a single stored procedure from a database

I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
1
vote
5answers
6k views

Coding an accounting database from scratch?

My new medical practice is picking up fast and my homemade Libreoffice Calc spreadsheet is quickly becoming cumbersome to track patient visits, accounts receivable, overhead reconciliation, etc. Most ...
1
vote
3answers
342 views

Design for archiving a sorted list (with ability for a few INSERT)

I have a somewhat static sorted list of words which I want to save in a database table: -- simplified! -- Note: the rank/order of each item is NOT calculable from other values! CREATE table words ( ...
1
vote
1answer
103 views

Issues with a Table having 120 fields in mysql

I am creating a table with 120 fields in MySQL. It will have 50 varchar(255) fields, other are maximum fields with char(1) , 1 date field and 5 double fields and others are int(11) fields. My ...
1
vote
3answers
425 views

Which RDBMS has the most advanced “table-valued” functions?

Every Database Management System focuses on specific features and applications. I am using mysql, probably as it is popular and satisfies common needs. However, I came across a problem, which needs ...
1
vote
1answer
3k views

mysqldump not escaping single quotes properly

I use mysqldump to backup my mysql database. The problem is that the sql file generated by mysqldump doesn't escapes single quotes properly. Here is an example of the mysqldump generated sql script : ...
1
vote
2answers
394 views

SQL queries break our game! (Back-end server is at capacity)

We have a Facebook game that stores all persistent data in a MySQL database that is running on a large Amazon RDS instance. One of our tables is 2GB in size. If I run any queries on that table that ...
1
vote
2answers
367 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I trying to attach this on two fronts, one I'm trying to code a way around it but secondly, I'm thinking some real optimization might help in the ...
1
vote
2answers
575 views

MySQL: many Sleep processes

I've queried SHOW processlist as root after a server restert, and got a long list of Sleep threads: +-----+-------------+-----------------+---------+---------+------+-------+------------------+ | Id ...
1
vote
2answers
3k views

How to analyse innodb status on deadlock in insert Query?

I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status. OS WAIT ARRAY INFO: reservation count 247864, signal count 247470 Mutex ...
1
vote
2answers
147 views

How to remove tables and indexes?

I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed. How to remove table and indexes ...
1
vote
2answers
209 views

SQL query based on foreign key relationship

I have two tables, a Project and an Action: Project has the following fields: project_name Action has the following fields: action_name project status There is a one-to-many relationship ...
1
vote
1answer
116 views

MySQL performance slows down when accessed from different regions via the Internet

I have a MySQL database hosted in a server in Brazil. When I connect through an ODBC connection (FE is access) with a Brazilian IP everything runs fine. When I connect from the U.S. with the same ...
1
vote
5answers
4k views

Tuning MySQL to consume less memory

I have a VM which has 2GB Ram, (full specs) And I am setting up a site which has one table in particular with over a million records. There's little or no usage of this particular database (perhaps ...
1
vote
2answers
519 views

Count consecutive null rows from a joined table

I have the following tables: members: ---------------------------------------------- | member_id | member_name | member_join_date | ---------------------------------------------- events: ...
1
vote
2answers
186 views

What MySQL storage engine should I choose?

I have a database for my catalog webpage. Some tables are about 3GB with millions of rows. What engine should I choose for my database, InnoDB or MyISAM? There will only be about 1,000 new records ...
1
vote
1answer
230 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);
1
vote
3answers
431 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
244 views

Are JOINs on non-indexed columns faster than sub-queries?

I know JOIN on indexed columns are faster than sub-queries in a query result. I am confused if : JOINs on non-indexed columns are also faster than sub-queries or not.
1
vote
1answer
2k views

Why I cannot let EXPLAIN update statements?

I can imagine that it's actually more important to explain SELECT statements, but I wonder why there's no possibility to use EXPLAIN on UPDATEs/INSERTs. I may use a WHERE-statement in an UPDATE where ...
1
vote
2answers
1k views

How can I optimize this mySQL table that will need to hold 5+ million rows?

The table has about 12 rows. Indexes: idint(11), Unique, auto-increment, primary ATTACHMENTIDvarchar(14), Unique MLSNUMBERbigint(20) POSITIONint(10) FILETYPEvarchar(32) I'm doing an extended ...
1
vote
2answers
362 views

How to DELETE duplicate entries on a field

I have a huge table that has 1,990,546 users with numerous data. Sometimes I get email lists and dump them into this table also. I want to delete users with duplicate email addresses. However, I ...
1
vote
1answer
245 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
937 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 = ...
1
vote
1answer
66 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 ...
1
vote
1answer
4k views

Do you still use MyISAM or prefer Aria storage engine?

If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery: Why still use MyISAM ? Should changing storage engine from MyISAM to ...
1
vote
1answer
787 views

Triggers vs Views in MySQL

We are in the process of denormalizing a large amount of data for performance reasons. Basically a Query that would normally JOIN over 10 tables is replaced with a Query that has no JOINs, yet all the ...
1
vote
4answers
482 views

cross self join to get more rows - better solution?

I have a table employees: +----------------+------------------+ | Field | Type | +----------------+------------------+ | id | int(10) unsigned | | persons_id | ...
1
vote
2answers
863 views

Which is better for long term database scalability: adding columns or having key/value store

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach: to add new columns to settings table every time new ...
1
vote
1answer
638 views

when will Innodb support activation of triggers on cascade foreign key? [closed]

The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html Currently, cascaded foreign key actions do not activate triggers. Any Idea when will this be implemented. Is ...
1
vote
1answer
72 views

How can this data be joined?

Definition: table_A table_B id,description id,description,id_table_A Column id_table_A is a foreign key to the primary key of table_A and is not mandatory. Sample data: ...
1
vote
1answer
1k views

Soft delete: Copy row data to archive table when deleting

How can I move data from my current table to an archive table when a delete is called on the table? My current table and archive table needs to be in sync in their schema too. Can someone help ...
1
vote
2answers
842 views

Very slow performance from MySQL

I have the following procedure: drop procedure if exists deletebatches; create procedure deletebatches() BEGIN declare acount int default 0; declare done INT default 0; declare abatchno ...
1
vote
3answers
469 views

GRANT command not working in MySQL Server

I'm trying to execute the below command as root user to provide accessiblity from my application. GRANT ALL ON communitycourts.* TO 'root'@'%' WITH GRANT OPTION; But it returns 0 rows affected, but ...
1
vote
1answer
1k views

When to open your MySQL cursor

A relatively simple question but from all my research I have been unable to find a decent answer. Which is technically more correct, open your cursor after your handlers have been defined or before ...
1
vote
2answers
3k views

When to create multiple-column index?

When to create multiple-column index in Mysql? Should I measure the slow queries and create multiple-column index for them?
1
vote
1answer
5k views

MySQL Replication Error

I am getting an Error on Slave as: could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error ...