Aaron Brown
  • Member for 10 years, 3 months
  • Last seen more than a month ago
Get profiles values where message.sender_user_id = profile.user_id
Accepted answer
3 votes

First, what is "wrong" with the data you are returning? My guess is that you intend to be using an [INNER] JOIN which will only return rows where there is a matching record, not a LEFT JOIN which ...

View answer
Recommended MySQL statistics for production environment
Accepted answer
3 votes

Monitor everything that you can as frequently as you can. I highly recommend Graphite w/ statsd as a central location to collect all your metrics. It provides a very simple plaintext protocol that ...

View answer
mysql dummy where check to utilize multi column index
Accepted answer
3 votes

It is doubtful that the optimizer would choose to use this index, and if it did, MySQL would need to do a complete index scan in order to match the second entry. This would be less efficient than a ...

View answer
MySQL: Delete all but last N records
3 votes

Try this exclusion join which will delete any ids that are not matched by the subquery. DELETE p FROM pixels p LEFT JOIN (SELECT id FROM pixels ORDER BY creation_time DESC LIMIT 5) p2 ...

View answer
Database Administrator vs Database Architect?
3 votes

Except in the most formal of companies, specific titles are largely meaningless and DBA can mean nearly anything. Some DBAs are operationally focused, some are development focused, some are analytics ...

View answer
Sort buffers and tables on disk, MySQL/InnoDB
Accepted answer
3 votes

If you are still seeing on-disk temporary tables with your max_heap_table_size and tmp_table_size set that high, then chances are you have a handful of queries with GROUP BY/ORDER BY (or a complex ...

View answer
How to recover previous values in Mysql Table?
Accepted answer
3 votes

unless you have a delayed replica, the only way to recover your old values is to restore the table from a backup. sorry.

View answer
Mysql master-master permission replication issue
Accepted answer
3 votes

Your grants are not replicating because you have an (unnecessary) replication filter on INFORMATION_SCHEMA. There is no reason to exclude this, as it is not replicated anyhow. When replication ...

View answer
Is splitting a 'users' table for authentication purposes a good idea?
3 votes

Do you actually have 2 million users? Unless you already have this problem or are certain that you will, you are optimizing way ahead of time. Add a compound index on login and password fields and ...

View answer
MySQL vs SQLite on Amazon EC2
3 votes

If you are concerned about the cost of iops, which are incredibly cheap in Amazon, then you are talking about a scale which sqlite will not be able to handle. There is no direct way to compare iops ...

View answer
MySQL Statement not displaying some values
2 votes

Your query is just plain invalid, as you have included values in the column list that are not part of the GROUP BY. MySQL lets you do this (unfortunately), but the results will be indeterminate. ...

View answer
Scalable DBaaS that can scale to hundreds of terabytes with easy backup and restore
2 votes

The only backends that I am aware of that can scale to hundreds of TB are Hadoop and Bigtable. There are absolutely no backends that support hundreds of TB that supports easy backup and restore. ...

View answer
Writing transactions to .MYD file to take Backup
2 votes

You need to run sync at the OS level to sync the fs cache to disk (assuming a *nix OS).

View answer
MySQL - INSERT INTO ... SELECT ... UNION
2 votes

Rolando answered your question regarding the INSERT syntax, but I'd like to add some additional comments regarding the performance of this query. First, you are DISTINCTing too many times. You ...

View answer
What are the limits on the amount of slaves per master?
2 votes

There is no hard limit to the number of slaves you can attach to a master, but there is a practical limit and it's based on your workload and hardware. Let's assume that you have a "bunch" of ...

View answer
`SELECT FOR UPDATE` in master-master mysql setup
Accepted answer
2 votes

Generally, it is considered a very bad idea to write to both masters in master/master replication, unless you are writing to different schemas. MySQL replication is asynchronous (MySQL 5.5 has "semi-...

View answer
MySQL Locks while CREATE TABLE AS SELECT
2 votes

In addition to setting the transaction isolation level to READ COMMITTED (or READ UNCOMMITTED), you have to also have your binary log format set to MIXED or ROW. STATEMENT based replication locks ...

View answer
MySQL: Slow log; append CPU usage
2 votes

While I don't know of any tool that provides this information specifically, Percona Server does have a setting (log_slow_verbosity=full) that enables extended statisitics in the slow log along with ...

View answer
MySQL wont start after modifying /etc/my.cnf
2 votes

You say that you are trying to change the MySQL port from 3306 to 512. If that is correct, then then problem is that all *nix (Linux, Solaris, AIX, etc) require you to run as root in order to bind to ...

View answer
Is there a generic framework/design pattern/library for real time replication of MySQL database onto other servers?
2 votes

Perhaps I am misreading this, but I am having difficulty understanding how your requirement is any different from built-in MySQL replication. With MySQL replication, the master database writes ...

View answer
Tons and tons of relay logs on a master
2 votes

Without seeing your my.cnf, it's impossible to answer this question, but I would also suggest posting your SHOW SLAVE STATUS\G output - is it possible that your slave is actually incredibly far behind?...

View answer
Could running ANALYZE on all databases on large InnoDB tables show some performance increase?
1 votes

There are a few situations where running ANALYZE TABLE is beneficial When you have innodb_stats_auto_update=0 set When your statistics are already "messed up" and need a fixin' If your table ...

View answer
--replicate-wild-ignore-table doesn't work?
Accepted answer
1 votes

This may be somewhat surprising, but this error is proof that your replication filter is working. The statement that has stopped working is an UPDATE to another table, but the update is using data ...

View answer
How to restore mysql DB from only myi files?
1 votes

No, it's not possible - you need the MYD file, too. The MYI file is only the indexes and does not contain any data. It is like trying to restore the contents of the book High Performance MySQL from ...

View answer
Fastest way to move a database from one server to another
1 votes

Having done a 5.1->5.5 upgrade several times, here's how you upgrade from 5.1 to 5.5 with a second server. Shut down the 5.1 instance Copy the data directory (often /var/lib/mysql) from the 5.1 ...

View answer
How to detect whether MySQL server is running as Amazon RDS
1 votes

Try SELECT @@version; I believe that Amazon has their own build of MySQL and would be apparent there. EDIT 2012-05-30 @@version does not return a unique string, but it appears that all RDS ...

View answer
How to make MySQL 5.1 connection ignore kills?
1 votes

I assume that you are referring to this article which Baron wrote for the Engine Yard blog... While he did provide some ways to reduce the pain of implementing a queue in an RDBMS, the real takeaway ...

View answer
apache hang on deadlock
1 votes

MySQL has built in deadlock detection and detects a deadlock as soon as it occurs. If you need to detect deadlocks, you will have to examine the error message coming back from the failed transaction, ...

View answer
What does Threads_connected=-1 signify in MySQL?
1 votes

Because you have -r for relative specified in mysqladmin, the -1 indicates that someone disconnected since the last sample. If you want to see the absolute values, remove -r.

View answer
MySQL replication risks for the master
Accepted answer
1 votes

If binary logging is already turned on and you are simply creating a new slave, unless you are nearing the limit of network bandwidth on the master (in which case, you have bigger problems), there are ...

View answer