MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

7
votes
3answers
11k views

Any better way out of MySQL InnoDB log “in the future”?

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
14
votes
2answers
3k views

How Does Table Partitioning Help?

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
7
votes
7answers
6k views

Get the rank of a user in a score table

I have a very simple MySQL table where I save highscores. It looks like that: Id Name Score So far so good. The question is: How do I get what's a users rank? For example, I have a users ...
27
votes
9answers
7k views

Is there a tool like Microsoft's “SQL Server Profiler” for MySQL?

While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler. For ...
12
votes
4answers
12k views

How do you identify InnoDB table corruption?

I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
5
votes
3answers
3k views

MySQL general query log performance effects

One of our customers who has a somewhat problematic web application would like us to log all the queries for a period of 24 hours. I wouldn't know how much data it will actually write. The amount ...
9
votes
3answers
11k views

Is it safe to use innodb_flush_log_at_trx_commit = 2

I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
5
votes
1answer
2k views

Capabilities of InnoDB INSERT Performance

Hi I am running the most recent version of Percona Server. Server version: 5.5.24-55 Percona Server (GPL), Release 26.0 I have a 10 cpu box of these characteristics. processor : 0 vendor_id ...
27
votes
5answers
3k views

When is the right time to use MariaDB instead of MySQL, and Why?

What we earn and what we lost with this migration? What should I expect as drawbacks after the migration? Is it really unnecessary to change the applications in any situation?
12
votes
2answers
5k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
12
votes
5answers
4k 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 ...
10
votes
1answer
2k views

MySQL table with 100,000 records queried often

I have a single database of about 100 tables to store various kinds of information. The most important table is our order table which is used to store customers orders and is over 100000 records as ...
9
votes
4answers
6k 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 ...
9
votes
1answer
6k views

InnoDB create table error: “Row size too large”

We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing ...
6
votes
3answers
5k views

InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

This server is a virtual machine running CentOS 6.0, MySQL 5.5.21 There is a database named devSystem. There are InnoDB tables within. Running the following commands causes error ERROR 2006 ...
6
votes
2answers
965 views

MySQL warm procedure

How do you usually warm up your database ? I run a similar queries to this for every table in DB: SELECT * FROM ip_log ORDER BY ID; SELECT ip, member_id FROM ip_log ORDER BY ip, member_id; Is that ...
4
votes
1answer
1k views

Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes

I just started dabbling with query cache for our CMS. Can anyone tell me why (or at least a good guess) I get alot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free? ...
20
votes
4answers
5k views

Why does MySQL not have hash indices on MyISAM or InnoDB?

I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
5
votes
2answers
22k views

MySQL: how to restore table stored in a .frm and a .ibd file?

I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install ...
4
votes
2answers
6k views

Is InnoDB Engine up to speed against Memory Engine?

I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows: SELECT P.col1, P.col2, ...
3
votes
1answer
577 views

Configured MySQL replication but its not working

I have configured MySQL master and slave replication setup on 5.5.x. But for some reason data is not getting replicated properly. What might be the reason? At Master: /etc/my.cnf [mysqld] ...
3
votes
2answers
833 views

Concurrently update with limit mysql

I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db. After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned. I ...
2
votes
2answers
3k views

Reset MySQL root password in LAMPP server on Ubuntu

I forgot the password I changed for the root user of MySQL. I'm logged in as root user and when I do # /opt/lampp/lampp security it gives result as XAMPP: Quick security check... XAMPP: Your XAMPP ...
1
vote
2answers
4k views

Set up a MySQL slave using mysqldump to get the initial data

I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
13
votes
5answers
5k views

In MySQL, does the order of the columns in a WHERE clause affect query performance?

I am having performance issues on certain database queries that have large possible result sets. The query in question, I have three ANDs in the WHERE clause Does the order of the clauses matter? ...
12
votes
1answer
12k views

INT(5) vs SMALLINT(5)

In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?
6
votes
2answers
4k views

Ibdata usage and Recommendations?

I have a Production server On November 5,2011 the ibdata size was 100G. In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G. I ...
5
votes
2answers
645 views

Choosing MyISAM over InnoDB for these project requirements; and long term options

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific. My project's aim is to let users search a (huge) database of variety of products. ...
5
votes
1answer
623 views

Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
5
votes
2answers
818 views

Why does MySQL choose this execution plan?

I have two queries, select some_other_column from `table` order by primary_index_column asc limit 4000000, 10; and select some_other_column from `table` order by secondary_index_column asc ...
5
votes
1answer
2k views

What is the difference between XtraBackup and rsync?

Yes, I'm going to run strace on them both... However, I would like to know ... What is XtraBackup is doing over and above rsync -ac src dest ? What's so special about XtraBbackup ? How is XtraBackup ...
4
votes
3answers
1k views

InnoDB vs MyISAM with many indexes

I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway: I have a ...
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 ...
3
votes
4answers
1k views

Benefits of BTREE in MySQL

What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage? Does BTREE provide easier iteration in increasing order ? What kind of queries would ...
3
votes
3answers
3k 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 ...
3
votes
2answers
1k views

Separate tables or partition one huge table?

I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
2
votes
1answer
1k views

Is there a progress indicator for OPTIMIZE TABLE progress?

MySQL 5.1.4x (Windows) | Innodb I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3; to reduce the ...
2
votes
1answer
2k views

How do I reduce the database size on AWS RDS MySQL?

I have an RDS MySQL database on Amazon Web Services. I want to reduce the size of the database in the control panel in order to save money. Q: How do I make it smaller? Their site says you have to ...
2
votes
1answer
1k views

importing myisam 5.0 database into a 5.5 innodb server

I have MySQL 5.0 and 5.5 servers (but not a 5.1 server). The 5.0 server is myisam only, the 5.5 default is innodb. I need to move a database from the 5.0 server to the 5.5 server. The database is ...
6
votes
4answers
1k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
6
votes
2answers
274 views

Less restrictive query return less result due to simple removing one additional constraint

Look at this query SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance FROM ...
6
votes
2answers
2k views

Why does MyISAM support FULLTEXT Searching and InnoDB does not?

I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
6
votes
4answers
4k views

MySQL gives “Out of Memory” error when inserting a large file. From where is this filesize limitation arising?

I'm using a MySQL database to store files. The table I'm using is structured as follows: +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default ...
4
votes
1answer
6k views

What can cause TRUNCATE TABLE to take a really long time?

I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves). I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands ...
4
votes
2answers
519 views

How to resolve the master server shut down/unavailability in mysql with master - slave replication

Let us suppose that we have a master/slave setup and the application is running fine for the time being. All of a sudden the master server suddenly shuts down due to some problem. Now how can I ...
4
votes
1answer
2k views

Suddenly have to rebuild indexes to prevent site from going down

I have been having this problem for a while: http://stackoverflow.com/questions/7770695/mysql-query-slow-until-reindex-column It seems all is good and when I suddenly get a lot of writes to the table ...
4
votes
1answer
856 views

Determining max database and table size supported and present size

One of my MySQL databases has been running on a single PC from the last four years. Here is my setup: MySQL 5.5.14 Windows XP on NTFS partition All of my tables are using InnoDB engine Here are ...
4
votes
2answers
650 views

With MySQL Replication, what level of resilience is possible?

I've found lots of good material on setting up MySQL replication, but not much on what to do in the event of failures. Understanding the degree of resilience I can achieve is fundamental to designing ...
4
votes
1answer
760 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 ...
4
votes
3answers
526 views

Separate databases for front-end and back-end

The site I am working on has a lot of backend/cron activity, so there are between 5 and 15 queries being executed every second of the day. This can slow down page loads by a second or more. I was ...