All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

3
votes
1answer
77 views

How to avoid listing FIELD/IN twice?

I have SELECT field(foo,'this','that','bar','baz') FROM t WHERE f1 IN ('this','that','bar','baz') How can I avoid listing ('this','that','bar','baz') twice?
1
vote
1answer
5k views

How can I select the latest record having one state where no later records exist with any other state?

I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget". widget state timestamp ------ ----- -------...
1
vote
2answers
2k views

Do I need SQL_NO_CACHE for write intensive query?

When I establish a connection to DB, I have to write about 30,000 records with an INSERT statement. I've read in a book that I should do it ! Questions Do I need to do SQL_NO_CACHE ? Is it gonna ...
4
votes
2answers
3k views

What happens when InnoDB hits its tablespace autoextend max?

The latest version of High Performance MySQL (3rd ed.) recommends that one: ...place an upper limit on the tablespace's size to keep it from growing very large, because once it grows, it doesn't ...
15
votes
1answer
26k views

Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week. I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual ...
5
votes
1answer
5k views

SELECT with large WHERE IN clause taking long time

I'm pretty new at all this so be gentle :). I have large medical DB where I need to extract data for patients based on selected doctor(s). Right now I build a patient list by SELECT patID FROM ...
5
votes
3answers
938 views

How can I select using the same fields from an undetermined number of tables using MySQL?

I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this: foo_reference1 foo_reference2 foo_referencea foo_referenceb .... ...
0
votes
1answer
375 views

horizontal partitioning or multiple tables?

I'm developing an application and need to propose a database schema. We plan for a MySQL database with millions of records which should be distributed over multiple machines for performance and ...
0
votes
1answer
9k views

MySQL: Why is DATEDIFF and TIMEDIFF/60.0/60.0/24.0 results different?

I have a database with two DATETIME values, and then I generate some new values from them: tdiff = timediff(d1,d2)+0 day1 = truncate(tdiff/60.0/60.0/24.0,0) day2 = datediff(d1,d2)+0 All three of ...
2
votes
1answer
11k views

How to compare (in MySQL) a DATETIME value to a TIMESTAMP value?

I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time). As I understand it, the DATETIME is set to ...
2
votes
2answers
2k views

Will an ALTER TABLE ADD COLUMN on a slave break replication?

I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with ...
2
votes
0answers
343 views

How to seperate sensitive data in database(MySql) [closed]

I need to design a database that will contains information about personal disease of users. What can be the approach in order to implement the columns of the DB's tables: encrypt the information, ...
1
vote
2answers
144 views

How to restructure this slow query

For the following query: SELECT l.* FROM `leads` AS `l` INNER JOIN `users` AS `u` ON l.client_id = u.id WHERE (`l`.`lender_id` = 1) AND (l.claimed_date IS NULL) AND (`l`.`disabled` = 0) AND (u....
2
votes
2answers
2k views

Model a family tree database in Access 2007

I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have ...
6
votes
4answers
28k views

Cannot GRANT privileges as root

Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all ...
5
votes
5answers
18k views

Mysql: Insert performance INNODB vs MYISAM

I am inserting into a simple small table with 5 attributes and 1000 rows. I observed when the engine is INNODB, each insert is taking 0.03 - 0.05 seconds. I changed the engine to MYISAM, then the ...
5
votes
3answers
2k views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
32
votes
2answers
41k views

When to use views in MySQL?

When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table? One reason that I would prefer to use views is that the database schema ...
2
votes
2answers
923 views

mysql products db design

I am building a products site with php and mysql(no shopping cart). I have 400k products, the main site with 2 methods for query. query for products types, all the query by menu, cloud tags (no ...
2
votes
2answers
137 views

Why isn't the following query using the `always` index?

This morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query: SELECT * FROM movies, ...
2
votes
2answers
2k views

Is there a benefit/drawback to adding LIMIT 0,1 when performing SELECT on unique key field?

I am using a MySQL database and perform many select like these: SELECT MyField FROM MyTable WHERE MyKey = 'some_value'; The purpose is to retrieve a field a value if the corresponding row exists. ...
8
votes
2answers
28k views

How do you remove fragmentation from InnoDB tables?

I have a Database having number of tables. I want to delete some records from the tables say the no of records are more than 20K or 50K. The All the Tables are InnoDB. And file_per_table is off. ...
1
vote
2answers
2k views

MySQL flush logs, unknown error

I gave been trying to solve this for few days now, and cant seem to figure this out. I have been trying to set log rotation for mysql slow using linux logrotate feature, and my script seems to get ...
1
vote
2answers
755 views

Strange behaviour with MySQL 5.1 TO_DAYS partitioning schema

I have this table definition: CREATE TABLE `va_ventas` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `Date` DATE NOT NULL, `Time` TIME NULL DEFAULT NULL, `Country` VARCHAR(3) NULL DEFAULT NULL, `...
1
vote
2answers
4k views

Slave settings not working

I have this machine as slave. Below is the command I ran and stuck. I done the change on the config file and set the id=2 too. Anything else I am missing here? slave start; ERROR 1201 (HY000): Could ...
1
vote
2answers
452 views

why can I only install MySQL 5.1 on cent OS 6.2?

I had Ubuntu 10.10 on my server and MySQL 5.1 version. But since i wanted to update the system, I installed a fresh version of Cent OS 6 only so I could install the latest version of MySQL. But after ...
4
votes
2answers
4k views

How to INSERT row in another database/schema in MySQL

Let's say I have the following database/schema setup with the following tables: SCHEMA_1 (default): TABLE_1 SCHEMA_2: TABLE_2 How do I insert rows in another schema's table?...
1
vote
1answer
4k views

Repair with keycache in mysql runs EVERY time. Can't even log why it's happening

I have a MyISAM table w/ ~1million rows and two indexes - one's a three-column key, and the other is a spatial index for bounded SELECT statements on a separate point column. My insert statements ...
3
votes
2answers
3k views

One query two MySQL servers

I have two MySQL servers on different server computers. Is it possible to execute single query like this: INSERT INTO database1.xxxxx SELECT table1.field1, table2.field1 FROM database2.xxxxx INNER ...
2
votes
1answer
2k views

How to sync two MySQL high load tables with 10M rows?

I have two identical MySQL innodb table with 10M rows each. One is used at backend while other at application end. Thus, either of them loaded. I need to synchronize them in a way which is fast and ...
1
vote
1answer
294 views

Log to record activity against a database

I've been assigned to create a general log. I need a log that captures what actions were taken, by whom, to the database. Regardless of whether it is a SELECT or an ALTER or etc. We need to keep ...
2
votes
2answers
2k views

How expensive are SELECT FROM LIKE statements from a design perspective?

I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files ...
1
vote
1answer
262 views

Unit testing with InnoDB transactions

So, I'm running some unit tests against some InnoDB tables in MySQL. Each unit test is wrapped in a transaction: setUp: SET autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ...
3
votes
2answers
7k views

What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?

I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-) I ...
2
votes
2answers
2k views

Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB?

Title says it all. However i have to enter at least 30 characters.
0
votes
2answers
73 views

I couldn't compile this little query (needs 2 tables)(mysql) [closed]

i have 2 tables as like seen : user table : id, userid , country friends table : id, userid , friendid so i want to get friendid rows from friends table where friends.userid == user.userid and ...
2
votes
1answer
89 views

if-clause in select query

I want to fetch some persons data from two tables via equi join. Some persons have address data and some have not. For those persons who haven't the addresses_id from table persons is NULL. For all ...
5
votes
3answers
7k views

Mysql users deleted

I have a fresh centos 6.2 with mysql in 5.1.52. Then I saw two empty user name and I deleted that then I saw 3 root user deleted that and created one like this CREATE USER 'root'@'%' IDENTIFIED BY '*...
14
votes
5answers
15k views

Why using innodb_file_per_table?

There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
3
votes
2answers
104 views

How to make MySQL 5.1 connection ignore kills?

According to MySQL 5.1's manual, section 13.7.6.4 KILL Syntax, when you kill a connection, a thread-specific kill flag is set for the thread. The flag will then be checked from time to time, even ...
2
votes
1answer
92 views

Change-set SQL optimization

I'm trying to clean up a change-set query that returns rows from a table that have changed since a given time and also do not exist in a known list of already previously returned results. select ID, ...
10
votes
2answers
10k views

PHP -> Mysql persistent connection pooling WITHOUT mysql_pconnect - Possible?

I have been trying to figure out a nice way of doing this for a while now. But have had a hard time finding the right pieces to do this. I am guessing this must be possible. To put it in simple terms ...
6
votes
2answers
23k views

MySQL case sensitive table names on Linux

Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't ...
2
votes
3answers
1k views

InnoDB UPDATE slow need a better option

I'm using MySQL 5.5 with a table in InnoDB running: deleteme tinyint NOT NULL DEFAULT 0;` UPDATE `import` SET `deleteme` = 1; -- Set the delete field LOAD DATA LOW_PRIORITY LOCAL INFILE "...
1
vote
1answer
68 views

Joins in MySQL database

I am a newbie to the MySQL database.I want to know different types of joins in MySQL and any link or any other kind of documentation with examples which will clear the doubts will highly appreciable.
2
votes
1answer
5k views

Concept to keep track of changes in all tables

In my current project I use about 10 MySQL tables (Doctrine as DBAL). I'd like to keep track of all changes in the databases (insertions, updates and deletions). At the moment there are two ...
2
votes
2answers
900 views

How to speed up audit trigger?

The task: write a trigger to log all updates within tbl1 table for future audit users' actions. The tbl1 table: id INT(11) y SMALLINT(6) country SMALLINT(6) -- really here ~20 fields but only some ...
1
vote
1answer
438 views

Creat Temporary Id in SQL Statement

I have a table id name 1 Alpha 2 Alpha 3 Beta 4 Charlie 5 Charlie I want to assign a temporary id at the run time in the SQL statement, So the desired result should be like myid name ...
2
votes
2answers
1k views

Trying to optimize query for performance (MySQL)

So I have this table that has a lot of foreign keys but when I will a record, I need to join all on those tables because I generally need those fields for the record to make any sense. The basic ...
0
votes
1answer
46 views

MySQL Conversion to DATE Type

What is the difference between the return values for these two expressions in MySQL: date('2011-01-01') cast('2011-01-01' AS DATE)