MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
2answers
552 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
785 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
170 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
984 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
688 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
511 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 ...
1
vote
1answer
325 views

“SQL_NO_CACHE” for innodb buffer pool

I'm guessing the answer is no, but I was wondering if there was anyway to tell innodb to not store fetched pages in the buffer pool? The reason for looking at doing this is check summing tables. I'd ...
2
votes
2answers
1k views

Request suggestion for a database between MySQL and Postgresql

We maintain a list of all the SAN Switch Ports that are either free or allocated to hosts. Currently, we enter the information on the company portal. But we need to move if off to a database because ...
10
votes
4answers
9k views

Mysql int vs varchar as primary key (InnoDB Storage Engine?

I am build a web application (project management system) and I have been wondering about this when it come to performance. I have an Issues table an inside it there are 12 foreign keys linking to ...
1
vote
1answer
2k views

MySQL Very Slow for REALLY Simple Operations

Something appears to be wrong with my MySQL even with very, very simple operations on almost empty (~100) tables E.g. DROP TABLE IF EXISTS hookup_gender takes 10+ seconds mysql> show processlist; ...
1
vote
1answer
2k views

“show create table” output with out “show”?

What I'd like to be able to get the SQL statement by 'show create table' as something I could do something with along the lines of insert into mytable (myTextColumn) (show create table blah) I've ...
2
votes
1answer
654 views

Number of locks exceeded when adding foreign key constraint

I'm adding a foreign key constraint to table a (17mil rows) that references table b (15mil rows): >ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id); ERROR 1206: The total number of locks ...
2
votes
0answers
245 views

why is MySQL 5.1 on ubuntu 10.10 not starting? [closed]

I just installed a MySQL 5.1 server on Ubuntu machine. after installation the server should automatically start but it does not start and gives me an error start: Job failed to start . Even when I ...
4
votes
2answers
3k views

Does the max_allowed_packet variable have any effect on LOAD LOCAL INFILE?

As the question states, what (if any) is the effect of max_allowed_packet on LOAD LOCAL INFILE? Does setting it to a large value load data files of large size such as 700MB into the db quickly?
-3
votes
2answers
516 views

SQL Server equivalent to MySQL UNIQUE index?

In MySQL (5.1) I am able to create a Index on a field that is type UNIQUE. This ensures that the field contains only unique items. Is there something equivalent to that feature in SQL Server 2008? ...
5
votes
1answer
2k views

Bit vs. Boolean columns

Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way. Does it actually provide any benefit using a bit field for a boolean value? From what I ...
5
votes
1answer
5k views

mySQL query optimisation — multiple joins or select … where not in (select distinct…)?

Background I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
1
vote
2answers
741 views

Commercial database systems versus open source database systems [closed]

Can anyone tell me what are the reasons for buying a commercial database system and not using an open-source free version? The reason for my question is that: Commercial systems cost upwards of ...
13
votes
5answers
7k views

How do I force one record to have a true value for a boolean column, and all others a false value?

I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table. Basically, I want to guarantee that this query will always ...
2
votes
3answers
191 views

Synchronise two databases (but not really)

I'm trying to design the following system. An arbitrary number of of embedded systems will each record data constantly and indefinitely. It is required that these devices upload their data to a ...
4
votes
2answers
7k views

SQL Query to get last day of every week

Using MySQL, I need to query a table that has a datetime field. I need to count all the records for a particular week. Using date_format(colname, "%Y %V") returns the week #, but I need the last day ...
2
votes
1answer
300 views

what does SHOW /*!50000 GLOBAL */ STATUS do ?

What is the difference between SHOW /*!50000 GLOBAL */ STATUS and SHOW GLOBAL STATUS I saw that in few scripts and tuning presentations the first command is being used. Is there a difference, ...
6
votes
1answer
241 views

Why does Oracle keep working on MySQL 5.1

2 years ago the first version of Mysql 5.5 was released. What is the reason to continue working on MySQL 5.1 - the current release is Mysql 5.1.62 It looks like there are should be fundamental ...
2
votes
1answer
705 views

MySQL SBR vs RBR

Assuming a mixed replication of statement based replication and row based replication in MySQL 5.5. How exactly does it work in a master-master scenario? Does the master that receives the query ...
2
votes
3answers
1k views

Storing Changes To Records

Everything I am talking about relate to relational database, specific MySQL. I have a number of tables in a database and for a moderate number of them, I am going to want to store a history of the ...
4
votes
1answer
911 views

Does the order of columns has a significant effect on INSERT?

Do we experience a noticeable difference in performance for these two queries? INSERT INTO table (col1, col2, col3, col4, col5) ... and INSERT INTO table (col5, col3, col1, col2, col4) ... Do we ...
1
vote
1answer
425 views

Difference in between MySQL affected num rows and num rows

I am just newbie to the MySQL.I want to know what is the main differences in between mysql num rows and mysql affected num rows?Any example or references will be highly appreciable.
0
votes
0answers
435 views

What's is WITH QUERY EXPANSION MODE in MySQL Fulltext Search

MATCH (col1,col2,...) AGAINST (expr [search_modifier]) search_modifier: { IN NATURAL LANGUAGE MODE | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION | IN BOOLEAN MODE | WITH QUERY ...
1
vote
2answers
4k views

Why is this MySQL proc using cursors failing to retrieve results?

I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, ...
-1
votes
2answers
1k views

Data Base Administration Oracle vs MySql? [closed]

I have worked with MSSQL for a while now, but now we want to try to move to some open source for a specific part of the company's products or explore Oracle if possible. What would you say are the ...
8
votes
2answers
5k views

Percona vs MySQL

What is Percona? How does it differ from MySQL? When should we consider switching (or upgrading) from stock MySQL to Percona? To add some specifics in our situation we almost exclusively use ...
11
votes
2answers
20k views

How to SELECT from SHOW TABLE STATUS results

I'd like to limit the rows and columns that come back from the SHOW TABLE STATUS command in MySQL 5.1. Is there a way to get this same information through a SELECT statement so I can manipulate ...
2
votes
1answer
244 views

Check against multiple duplicate records

I have data that i should insert into table. But before inserting i need to check against duplicate records and report list of those records. Table: CREATE TABLE `test` ( `A` varchar(19) NOT NULL, ...
1
vote
1answer
4k views

How to use ON DUPLICATE KEY for UPDATE

Consider a table of CREATE TABLE test ( id int(11) unsigned NOT NULL AUTO_INCREMENT, external_id int(11), number smallint(5), value varchar(255), UNIQUE INDEX (external_id, number), PRIMARY KEY(id) ...
3
votes
1answer
200 views

Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

Is there any different how mysql treats UNIQUE (column1, column2), INDEX (column1) and UNIQUE INDEX (column1, column2) and UNIQUE (column1, column2) In the latter, I think mysql will ...
5
votes
2answers
225 views

How should an object that can reference an object of multiple types be modeled in a relational schema?

Apologies for the obscure question, it may make more sense with a concrete example: In my application, I may create a portfolio that contains a set of projects. However, I may also add a ...
4
votes
2answers
2k views

Access denied for user 'myuser'@10.% to database 'mydb' - Mysql Import SQL Script

I have a database that I am trying to recreate at my web host. I used mysqldump to create a sql script. My web hosting company, IPage, has a hosted mysqladmin site where I am supposed to be able to ...
9
votes
1answer
4k views

What does 'ibfk' stand for in MySQL?

If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
1
vote
1answer
2k views

Does the order of keys in compound Primary key make any difference?

Package Package ------- PackageID PackageName CityID PRIMARY KEY (PackageID) UNIQUE KEY (PackageName) FOREIGN KEY (CityID) REFERENCES City (CityID) Package versions: PackageVersion ...
2
votes
4answers
3k views

Creating Archiving database for old data in MySQL

I Have a Master and Various Slave Servers. There is a huge amount of the data on all the servers and I wish to create an Archiving system for our Data. Scenario is : I want that only Last one year ...
1
vote
1answer
244 views

mysql event running at wrong time

I created an event to run every 24 hours and be run at 14h. Its selects some data from tabels and writes it as to a file (csv format) The query and writing to file goes ok. The event is triggered. ...
7
votes
2answers
15k views

How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings. Any idea on how to do a case sensitive search in SQL query?
3
votes
2answers
238 views

Create table statement in MYSQL

I am trying to create the following table in MYSQL and is giving errors, CREATE TABLE CUSTOMER ( CUS_ID INT NOT NULL AUTO_INCREMENT, CUS_NAME VARCHAR, CUS_DOB DATE, CUS_ADDR VARCHAR, CUS_EMAIL ...
5
votes
2answers
1k views

How do I get the execution plan for a view?

I have a schema with a number of views. I need to check the execution plans to make sure the appropriate indexes are in place and being used. How do I do this? I'd rather not have to copy and paste ...
0
votes
1answer
9k views

What's a good (free) client for MySQL on Windows? [closed]

I am almost repeating a question done here by another user, but related to MySQL. What do you think is the best free (if any) MySQL client software? Thank you!