MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
1answer
75 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?
0
votes
1answer
898 views

Problem with Error: InnoDB: page … log sequence … number is in the future!

i know this question was asked before, but i am at a point where i am really stuck and do not know how to continue with this problem and i would really like to know, what causes this error to come up. ...
4
votes
2answers
2k 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 ...
1
vote
1answer
2k views

MySQL MyISAM Locking question

It was my understanding that MyISAM uses table locking during inserts and updates. To me that meant that read and write by other queries on that table is disallowed until the first insertion or update ...
4
votes
3answers
1k 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 ...
1
vote
2answers
1k views

Mysql Replication Automation Script

I am looking for something that will help us to create a lot of mysql slaves connected to the specified master, let's say kind of mass-replication. If there are any existing solution for this ...
3
votes
3answers
462 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 .... ...
2
votes
1answer
6k 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 ...
0
votes
1answer
4k 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
2answers
1k 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. ...
2
votes
0answers
269 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
138 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 ...
7
votes
2answers
6k 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 ...
2
votes
2answers
616 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
129 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

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 ...
1
vote
1answer
2k 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 ...
1
vote
2answers
359 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 ...
3
votes
2answers
2k 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 ...
1
vote
1answer
143 views

Two MySQL query results are different, but shouldn't be

To the best of my knowledge, these results (especially the data in the 'destination' field) should be the same, but I can't find out why they're different: mysql> select * from cc_did_destination ...
1
vote
2answers
857 views

How to eradicate Mysql Slave Replication delays when huge updates from master

At my environment we had slave replication with mysql server version 5.1.47 enterprise version.When ever there is huge updates from master, slave is lagging behind the master. What is the best ...
2
votes
1answer
84 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 ...
2
votes
2answers
1k 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 ...
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 ...
2
votes
4answers
1k views

Very small MySQL table keeps crashing

I have a MyISAM MySQL table that keeps crashing. The table has only 16 rows and is very rarely written to (maybe once a month). I have a medium-sized site that typically has about 500 pageviews per ...
2
votes
2answers
971 views
3
votes
2answers
5k 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 ...
3
votes
2answers
91 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
467 views

Can I choose which CPUs / Cores to be used by MySQL?

I have a Windows VPS (Windows 2008 R2 x64) that runs a couple WordPress blogs and other few ASP.NET things. The VPS runs 2 CPUs (for example). With ASP.NET, in such situation, I usually set the app ...
3
votes
1answer
135 views

What are some key configuration settings for using an InnoDb table for session storage?

I want to start clustering my PHP app servers but don't want to go for a sticky-sessions load balancing setup at this point. However, I want sessions that are persisted to disk and implement proper ...
2
votes
2answers
999 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 ...
1
vote
1answer
65 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
3k 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 ...
1
vote
1answer
223 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
3answers
2k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
2
votes
1answer
669 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 ...
0
votes
1answer
36 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)
3
votes
3answers
1k views

Estimate large database size and speed

First I want to know how to estimate the database size regarding the biggest table it will contains. I've the following : +----------+------------------+------+-----+---------+-------+ | Field | ...
4
votes
3answers
229 views

Most efficient (or meaningful) way to index grid-based data

I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have ...
3
votes
2answers
567 views

MySQL cache is not getting hit by subqueries

I noticed something weird on MySQL query_cache behavior and I would like to know if this is a normal behavior. Let's say I have an item table ID | Item ----------- 1 | Item_1 2 | Item_2 3 | ...
1
vote
1answer
792 views

How does innodb_stats_on_metadata relate to query execution plan stability?

The MySQL documentation for innodb_stats_on_metadata says "It can also improve the stability of execution plans for queries that involve InnoDB tables." That is a little vague. What is an example ...
2
votes
2answers
171 views

Seconds Behind Master in Cloud Service

I have few mysql servers in replication master-salves in cloud service. The problem is even thought all configuration, mysql versions, server's resources are the same, still I have inconsistency in ...
4
votes
4answers
3k views

Is raid 5 suitable for a mysql installation?

Is raid 5 suitable for a mysql installation? Let me explain my application further. My application is a socket programming which will connect with gps device to receive gps string and there after do ...
1
vote
1answer
238 views

time executing inserts + slow SSH

_ Hello, my setup is a slow SSH to some linux machine. From there I start a mysql terminal client and execute about 200k inserts (in a while loop inside a stored procedure). This takes about 3 hours ...
1
vote
2answers
1k views

How to install MySQL server on Mac (OS X 10.6.8) from command line?

How to install MySQL server on Mac (OS X 10.6.8) from command line? If I donwload the .dmg file, is it possible to install it from command line?
9
votes
3answers
8k views

Is innodb_file_per_table advisable?

We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with innodb_file_per_table or leave ...
2
votes
2answers
74 views

Delete all highscores from a certain country

I have two tables: tbl_players tbl_highscores I need to delete all highscores that are registered from a certain country. My problem is that the language are set per player, rather than by ...
2
votes
1answer
712 views

How to use the 'select benchmark' command on a procedure

I am trying to check which of my procedures is slowest and thus needs optimization. I currently have around 160 procedures and about 30 functions. I am using the select benchmark() to check the ...
2
votes
0answers
521 views

mysql pre-start process (23901) terminated with status 1 [closed]

I installed a new Mysql server version 5.1 on machine which was running on 5.0. Now when I try to start the mysql server using command service mysql start it gives me an error start: Job failed to ...
4
votes
3answers
3k views

MySQL slow log configuration

Is there a way of making MySQL slow logs to start new log file every day ? At the moment its just a single large file, and have to grep lines for every day. It would be much more convenient to have ...