MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.

learn more… | top users | synonyms

3
votes
1answer
346 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
2
votes
2answers
609 views

Does MySQL 5.5 support shared database with separate schema?

I am developing a SaaS based product using MySQL 5.5 as the backend database. I want to keep shared database for all the tenants, but separate a database schema of each tenant. There would be a single ...
2
votes
1answer
291 views

Null Values and Performance

I always try to build the database avoiding any NULL values in my tables, and I have successfully had done this so far. (Hey, I am very new to database design.) But still there are times when saving ...
1
vote
2answers
166 views

Is there a way to get slow-query-log-like profiling information on a single query?

Specifically, I'm looking for this information: # Query_time: 0.011922 Lock_time: 0.000088 Rows_sent: 107 Rows_examined: 663 Rows_affected: 0 Rows_read: 107 # Bytes_sent: 4152 Tmp_tables: 3 ...
0
votes
2answers
1k views

Some queries slower in MySQL 5.5 than 5.0?

We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran. We recently updated ...
0
votes
0answers
490 views

MySQL import file with no column delimiter

Is there a way to use the mysqlimport command on a file that has no column delimiters? I would like to import the ctl file used with the sqlloader for Oracle
1
vote
1answer
699 views

Does TRANSACTION/COMMIT/ROLLBACK only work on certain MySQL engines such as InnoDB?

Are transaction functions only relevant for particular database engines on MySQL, or are transactions supported under all MySQL engines, including MyISAM and InnoDB? someone says here that ...
1
vote
0answers
87 views

How to profile individual queries using PERFORMANCE_SCHEMA

I've been looking at SHOW PROFILES, which seems ideal for what I am doing. However, I've noticed a warning that SHOW PROFILES is being deprecated and that PERFORMANCE_SCHEMA should be used instead. ...
2
votes
2answers
2k views

mysql 5.5 corrupt - missing temporary table

One of our mysql instances crashed and failed to start: 120422 10:04:13 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'! ...
1
vote
1answer
681 views

MySQL Master-Slave Replication Cross Database

I am setting up Replication for cross Database as i have set 2 variables in Slave' s my.cnf as replicate-do-db=database_name and Replicate-wild-do-table=database_name.% ,But i am not able to get ...
0
votes
1answer
59 views

Move databases on same Mac

I have just updated my MacBook to Mountain Lion. Since then, my MAMP installation does not work properly so I decided to use the build httpd/php stuff. For this, I have additionally installed a new ...
4
votes
2answers
10k views

innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster. We have 125G main memory with 24G InnoDB buffer pool ...
6
votes
1answer
4k views

When to switch from MyISAM to InnoDB?

We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM. My original plan was to upgrade ...
2
votes
1answer
91 views

Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0

I didn't know if this is possible using just a query or if I need some procedural logic on this one. I have this table: CREATE TABLE `default_messages_users` ( `id` int(11) NOT NULL ...
1
vote
1answer
2k views

MySQL Workbench ERROR 1064

I have a problem with my database. I have created the model and now I want to sync it with my database on my WAMP Server (local) but it keeps giving me an error. I searched for the cause of the ...
1
vote
0answers
85 views

MySQL replication backup

How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu. What happens if, I issue or schedule ...
3
votes
1answer
2k views

Slow insert performance in mysql innodb table for solr indexing

I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow. ...
2
votes
1answer
339 views

Assertion failure from InnoDB while restoring using Innobackupex

I thought I would give back to all those people who help me when I have a problem. This might be something old, but I did not find it anywhere so here goes: We recently upgraded our production DBs ...
-2
votes
1answer
233 views

Compare MongoDB and MySQL

Can anyone help me to make right decision for choosing scalable and reliable database? I have to create 3-4 tables with around 200 columns each and these tables will have around 5000 data records. As ...
0
votes
0answers
71 views

memc_delete called from a MySQL trigger loop fails randomly

I'm calling memc_delete from a MySQL trigger's code. The call is inside a loop. Logging the results, I see that memc_delete sometimes returns 1 (ok, memcached entry deleted) and sometimes returns 0 ...
5
votes
2answers
2k views

how to enable temporal database capability in mysql.?

How to enable the temporal database compatibility in mysql database? Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current ...
1
vote
1answer
163 views

Get and count all blog posts and all users that fits a LIKE condition (two sepparated queries)

I've this tables: -- -- Table structure for table `default_cmvblog` -- CREATE TABLE IF NOT EXISTS `default_cmvblog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) COLLATE ...
6
votes
2answers
15k 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
1answer
881 views

I am getting an error while trying to download binary for mysql 5.5.11

I am trying to upgrade MySQL 5.1 to 5.5.11 on Ubuntu 10.10. Everytime I try the wget command I get this error: wget ...
2
votes
2answers
484 views

MySQL - How much physical space will be reduced by changing the column type?

Lets say I have the following table: CREATE TABLE `my_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `relationship_status` varchar(48) CHARACTER SET utf8 COLLATE ...
0
votes
1answer
201 views

How to set unique columns combination

see this table: CREATE TABLE IF NOT EXISTS `default_messages_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `message_id` int(11) NOT NULL, `owner_user_id` int(11) NOT NULL, `to_user_id` ...
6
votes
1answer
5k views

Deleting bin logs in replication environment

I have a question about deleting binary logs in the replication environment: We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy ...
5
votes
3answers
5k views

Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB

I need to design database to store information contained in millions of log files generated by devices on trial on network. I have designed 5 different tables to store log file information. But, as ...
4
votes
1answer
76 views

Will these queries return the same results?

I have this simple query: SELECT friend_id FROM default_friend WHERE user_id = 1 And the results are below: +-----------+ | friend_id | +-----------+ | 2250 | | 4901 | | ...
2
votes
1answer
157 views

Need some help with this query get all status and their associated comments of me and all my friends

I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was ...
1
vote
1answer
120 views

Several values from different tables

I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest ...
0
votes
1answer
188 views

Is possible to optimize this query or it's already optimized?

I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one: -- -- Table structure for table `default_users` -- CREATE TABLE IF NOT EXISTS `default_users` ( ...
2
votes
2answers
493 views

Tool to match mysql password encryption

I've created a user ... but forgotten the password mysql> create user 'blayo'@'%' identified by 'right'; Which Linux command line tool can encrypt the password the same way mysql does ? mysql> ...
5
votes
2answers
217 views

Is there a reason/benefit I am not aware of for this kind setup?

I now manage a MySQL database that was designed and created by another. I am confused about a certain setup that the original developer created.. He did not use auto increments for table IDs. ...
1
vote
2answers
829 views

Warnings: Why are statements with limit clause unsafe even if there isn't a limit?

I'm a bit irritated because of some warnings in mysql errorlog. Example 1: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement ...
1
vote
0answers
179 views

How to speed up this query

MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200. That way innodb doesn't have to compute ...
2
votes
1answer
841 views

Extract some columns data from one table and insert into multiple tables

I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables tbl_1: id, name, lastname tbl_2: id, ...
5
votes
2answers
173 views

DELETE LOW_PRIORITY row visibility

I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and ...
4
votes
3answers
1k views

What are the limits on the amount of slaves per master?

I currently have a Master with 2 slaves, all running MySql 5.5. What are the limitations on the amount of slaves I can connect to a single master? what parameters should be taken into affect?
2
votes
2answers
2k views

Need suggestions for best way to archive MySQL (InnoDB) tables

Problem: I have two fairly large tables. A 'wall' table for messages between users with 9 million rows at 2 GB and a 'chapter' table at 2 million rows and 18 GB. I want to keep the number of active ...
2
votes
2answers
3k views

How to improve MySQL Server Performance..??

As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers. Now my question is where to start from like we will need to find out many things as 1.Find the ...
2
votes
3answers
1k views

MySQL backup with no downtime

I'm running a M/S replication of MySQL 5.5 / innoDB (over ubuntu). My DB is pretty large: ~250GB. I'm looking for a backup tool/service that will do the following: full backup no downtime / locks ...
1
vote
1answer
706 views

decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose ...
2
votes
3answers
4k views

innodb_buffer_pool_size not changed

I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf default-storage-engine = innodb innodb_buffer_pool_size = 3G innodb_log_file_size = 256M innodb_thread_concurrency = 8 ...
3
votes
1answer
58 views

where varcharcol = 0 returns all rows

Suppose I have a table like this: create table mytable(cola int primary key, colb varchar(10)); Let's put some data in it: insert into mytable values(1, 'abc'); insert into mytable values(2, ...
3
votes
1answer
217 views

Get profiles values where message.sender_user_id = profile.user_id

I have this tables: default_messages CREATE TABLE IF NOT EXISTS `default_messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `message` text ...
1
vote
1answer
128 views

slave issue with load data

When I use "load data infile" on master, the data is properly transferred to slave. But when I use "load data local infile" from remote server to master, the data does not pass on to slave. Why does ...
4
votes
1answer
8k 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 ...
10
votes
1answer
3k 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 ...
-1
votes
1answer
795 views

What exactly is SOLR? [closed]

It seems like a magical technology that can Do spatial search do full text search The problem is, It doesn't say where does it store all the data. Does it have it's own proprietary database If it ...