MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
2answers
173 views

MySQL Query Optimization : Bets and Averages

I have the following selection string: SELECT * , AVG( `q1` ) AS betavg, COUNT( bet_id ) AS nr, MAX( q1 ) AS qmax, (MAX( q1 ) - AVG( `q1` )) AS diff FROM jos_bet_1x3_best GROUP BY `bet_id` HAVING ( ...
1
vote
2answers
638 views

Are there any MySQL Triggers or Events that my program can wait for?

Say I want a live feed of emails on a web based email system. Emails are inserted into MySQL tables. Let's say I want my program to know immediately when the table is updated so it can check for new ...
4
votes
1answer
931 views

How does max_allowed_packet affect the backup and restore of a database?

I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps ...
3
votes
2answers
387 views

Character Set Encoding in a Table

I have the following data:Trollhättan If I print out and setting up the header to utf-8, then this is the output in the browser. If I'm not setting up the utf-8 in the header, Trollhättan. ...
7
votes
3answers
4k 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 ...
4
votes
3answers
1k views

MySQL Query Optimization : Indexing and Pagination

I'm adding pagination to my 'latest news' php script and running in to an issue. SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit I ...
3
votes
2answers
123 views

What would be the best way to model my simple table?

What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4? Or, searching for the right row through a table with only 1 column with ...
2
votes
2answers
588 views

Slow Query logging everything

I have a mysql instance that is logging slow queries. I have it set to 1 sec, but I am getting back lots of these:(example) Query_time: 0.000237 Lock_time: 0.000064 Rows_sent: 0 Rows_examined: ...
3
votes
1answer
522 views

A question on MySQL SELECT Algorithm/Function

Let's say I have 100,000 rows in a table, and each of the rows has a boolean value. Only ten of these rows have a value of 1, the rest are 0. How would MySQL perform a SELECT on only the rows with a ...
7
votes
3answers
14k views

Modify DEFINER on Many Views

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result. Got error: 1449: The user ...
2
votes
2answers
819 views

How to enable all the possible backup options of MySQL

I normally use MySQL Administrator to backup my database. I want to enable all the backup options for the available backup types in MySQL. I noticed that Binary Logging is not running on my PC. How to ...
4
votes
2answers
1k views

Creating partitions on a production database

I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals. I have two tables that involve a ...
0
votes
1answer
2k views

How to get query execution time and query result at the same time? [closed]

Is there any SQL query syntax that returns query execution time and query result at the same time?
2
votes
2answers
2k views

Does mysql SUPER privilege include ALL?

I'm granting the following rights to a user: grant super on . to 'user'@'host'; does this include the rights on dbname given by: grant all on dbname.* to 'user'@'host'; thank you
8
votes
2answers
3k views

What are the benefits of using MySQL Cluster?

I have never dipped my hands in MySQL Cluster and want to know how it can benefit me. When is it best to use it, and does it have performance benefits?
3
votes
3answers
689 views

Restoring an old backup to latest MySQL release

Our old database is MySQL 5.1 server and is four years old. If I uninstall MySQL 5.1 Server and install the latest release and restore the backup, will I gain performance benefits? I guess the new ...
5
votes
2answers
133 views

Optimizing a four-year-old MySQL database

I developed an application with MySQL 5.1 back-end four years ago. After that, I only gave remote support a couple of times, but that too for application crashes, and not for database. The database is ...
2
votes
3answers
12k views

Data Truncated for Column

DECLARE float_one, float_two, my_result NUMERIC(7,2) my_result = CONVERT(float_one/float_two, DECIMAL(7,2)); In this mysql query, I do this type of operation in a stored procedure, but the Linux ...
3
votes
1answer
6k views

DROP PROCEDURE IF EXISTS not included in mysqldump

I'm dumping my stored procedures only using the following command: mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql but the ...
7
votes
1answer
5k views

Dump only the Stored Procedures in MySQL

I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
4
votes
2answers
4k views

MySQL Deterministic Procedures

As a general rule, should I be declaring all my stored procedures with the DETERMINISTIC keyword if they are indeed deterministic? It seems to me that the majority of stored procedures will be ...
5
votes
1answer
3k views

my.cnf values not applying - Optimizing MySQL in CentOS LAMP

I have checked for other occurences of my.cnf and I have found only one occurrence of /etc/my.cnf. Then, I went ahead to fix in my values, e.g. query_cache_size etc. After saving and literally ...
2
votes
2answers
454 views

MySQL Data Synchronization

I have a user who will be away from the office for a while, but still wants to be able to insert a load of records to our MySQL database. The user will have a laptop, but no reliable internet ...
1
vote
1answer
328 views

Why did the MySQL Storage Engine Change on Restore in one scenario?

In my production database life, there was one tragic incident when the MySQL engine type was accidentally changed from InnoDB to MyISAM on restoring a recent backup. The result was that all ...
4
votes
2answers
4k views

Prevent copying to temp table (sql)

I have two tables - comments and votes : comments(id, text, user_id, page_id) votes(id, value) There are 2,000,000 rows in the votes table. I created the following query: SELECT ...
2
votes
2answers
755 views

Is MySQL Replication appropriate for keeping a laptop in sync?

I've setup Mysql master slave replication with the production server as master. I have three other machines as slaves: 1 laptop, 1 desktop, and 1 server. The desktop and laptop both get suspended to ...
1
vote
1answer
162 views

Complex mysql selection

I have the following table, id | booki_id | bet_id | bet | bettype | line | odds 1 | 123 | 321 | 1 | 3way | 0,0 | 2.3 2 | 123 | 321 | 2 | 3way | ...
13
votes
1answer
352 views

Does anyone have a good recommendation for a book which teaches sharding?

Does anyone have a good recommendation for a book which teaches db sharding (from scratch at best) I have read like 40 different websites talking about sharding. I'm not saying online sites/blogs ...
7
votes
6answers
6k views

Too many columns in MySQL

I have a table with 1699 columns and when I'm trying to insert more columns I get the too many columns in the table error. In this table I have only 1000 rows. For me the most important thing is the ...
3
votes
2answers
2k views

Different MySQL Datafile Sizes After Restoration

I'm quite new in database administration. The database architecture of my current company is master-slave replication. We are using MySQL version 5.0.86. Some weeks ago, we upgraded the company's ...
2
votes
1answer
2k views

MySQL - LOAD DATA INFILE - Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

I am selecting data from a database on server A, dumping the output into a file locally, and then doing a LOAD DATA INFILE on my local database. The bash command looks something like this: mysql -e ...
2
votes
3answers
1k views

MySQL and PostgreSQL Licence issue

Our Company wants to use MySQL for one software product. This product is not open-source. Is it legal to use it freely ? If not, how about PostgreSQL ? Is this okay for our purpose ?
4
votes
2answers
5k views

MySQL Event does not run

i am running mysql 5.1.41 that came bundled with xampp on windows. the problem is that the event doesnt run automatically even when the event scheduler is ON. i have a table named ta_table using ...
3
votes
2answers
93 views

Exclude dataset from a query

I have 2 tables EXAM (StudentID, SubjectID, SchoolYear, Period, Mark) and CANCELED_EXAM (StudentID, SubjectID, SchoolYear, Period). Primary key for both tables are (StudentID, SubjectID, ...
9
votes
1answer
4k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
12
votes
1answer
3k views

Is it possible to have passwords configured per database or per host in .my.cnf

I have the following in my ~/.my.cnf [client] password="somepass" but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different ...
2
votes
2answers
125 views

Database Agnostic Conditional

I'm writing a Drupal module and I want it to be agnostic between PostgreSQL and MySQL. I have a field that gets its value from a conditional, and I originally wrote it as an IF(). Then I found out ...
2
votes
1answer
107 views

MySQL user account 'From Host' setting

I've just installed Wamp server 2 and I get the following 4 default MySQL users: USER FROM HOST <anonymous> localhost root localhost root 127.0.0.1 ...
5
votes
2answers
551 views

Is it a good idea to split MySQL DB into two servers

I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate: I ...
2
votes
3answers
5k views

MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST

I'm trying to figure out why the reporting of slave lag is different in SHOW SLAVE STATUS and SHOW PROCESSLIST, in MySQL 5.5.13. The only difference to other slaves of this master is that it is ...
2
votes
2answers
994 views

mysql inner join producing more results than it should

I am trying to get the roles name, verbs name, and privileges enabled value where privileges is a join table joining verbs and roles through a verb_id and roles_id foreign key. But I am having ...
4
votes
2answers
1k views

MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */

Using mysqldumpslow, I can see that the most common entries in a MySQL slow query log are of the following form: SELECT /*!N SQL_NO_CACHE */ from wp_posts (or wp_comments). I can't seem to find ...
3
votes
1answer
7k views

How do I open a MySQL Database Dump?

I work for a doctor's office, and we are in the process of switching EMR systems. The previous system has provided a physical 'dump' of the database. I however, don't know how to open this database ...
1
vote
3answers
682 views

Best architecture solution for heavy data mining system, that can run on cloud

We are currently hosting our MySQL database on a dedicated server with lots of memory and processors. We would like to push it to a different architecture that would allow us to scale it easily, ...
4
votes
3answers
656 views

MySQL very slow query when changing one WHERE field despite no index/key

This is quite a confusing issue for me. I have a database full of baseball statistics. Running this query: SELECT * FROM hits JOIN stadiums ON stadiums.gameName = hits.gameName JOIN players ON ...
5
votes
2answers
2k views

What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES?

In MySQL's table INFORMATION_SCHEMA.TABLES, there's a column named 'TABLE_CATALOG'. The documentation is sparse on this column and I'm wondering what the heck is the purpose of this? Any killer-app ...
6
votes
3answers
6k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
1
vote
2answers
2k views

Distributed Transactions between SQL Server 2000 & MySQL Stopped Working

We have been successfully copying data from a MySQL Slave database into a SQL Server 2000 database. The MySQL server is a linked server. I have tried using v3.51 and v5.1.8 of the ODBC connector ...
3
votes
2answers
552 views

Testing Query Speed

When I'm running a query for the first time, it takes x miliseconds to be executed. When I run it the second time it takes a lot less time. I assume that it is some caching involved in this mechanism, ...
2
votes
1answer
1k views

Suggest a MySQL book for me [closed]

I have recently joined a team in my corp office that has mysql as a part of their work profile. Though the mysql work not often in this team, but when they do it comes in big scale. The main mysql ...