Aaron Brown
  • Member for 10 years, 3 months
  • Last seen more than a month ago
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
Sub-query executes 1 time for a parent query or more?
Accepted answer
8 votes

Prior to MySQL 5.6, the inner query is executed once per entry in the outer row and is easy to prove: mysql> SELECT COUNT(*) FROM sample_data; +----------+ | COUNT(*) | +----------+ | 8 | +-...

View answer
How can I speed up "show columns" on MySQL?
12 votes

MySQL recalculates table statistics for certain operations that access INFORMATION_SCHEMA tables (SHOW COLUMNS is just a convenient alias for querying INFORMATION_SCHEMA.COLUMNS). Set ...

View answer
Is it possible to pass parameters to pt-table-checksum using an options file?
Accepted answer
5 votes

Yes, you can do this. pt-table-checksum --config FILENAME Where FILENAME looks like tables = table1,table2,table3 ignore-tables-regex = ^tmp chunk-time = .2 max-...

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
How to manage millions of users?
32 votes

You're not going to have a billion users tomorrow and MySQL can handle several million rows without any problem. I have 5 million users in my user table and trust me, it isn't even on my radar of ...

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
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 replication - replicate to multiple DBs on slave?
Accepted answer
6 votes

The first obvious question is why would you want to do this? It's probably a bad idea and completely unnecessary, since you can join across databases: SELECT * FROM db1.table1 JOIN db2.table2 ON db1....

View answer
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
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
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 - 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
Mysql slows down temporarily after large file copying
5 votes

MyISAM tables rely on the filesystem cache to keep data in memory (the indexes are kept in the internal key buffer cache, as specified by key_buffer_size). You haven't said what your total data size ...

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
What's wrong with this MySQL query?
Accepted answer
4 votes

You are using the wrong quotes. Use ` instead of ' CREATE TABLE `topics` ( `topicid` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `creator` varchar(255) NOT NULL, `createtime` ...

View answer
Running SQL Query on all databases
8 votes

Try running this and then cut n paste the output back into phpMyAdmin SELECT CONCAT('UPDATE `', schema_name, '`.\'wp_users\' SET \'user_pass\' = MD5(\'somepassword\') WHERE \'user_login\' =\'...

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
Any disadvantages of "SQL_BUFFER_RESULT" for MySQL SELECT?
Accepted answer
10 votes

SQL_BUFFER_RESULT creates a temporary table on the server for every result set. This is not a temporary table like CREATE TEMPORARY TABLE - it is an implicit temporary table as would be created when ...

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
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
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
Why is MySQL is creating so many temporary tables on disk?
Accepted answer
18 votes

mysqltuner rarely provides any useful information. It uses mostly irrelevant statistics about "hit rates" and puts arbitrary limits on what is an acceptable number of widgets are acceptable. If you ...

View answer
Monitoring replication on mysql
Accepted answer
9 votes

Replication can break or misbehave in all sorts of fun and exciting ways. You need to monitor for three things: 1. Replication is running and has not stopped due to error To monitor whether or not ...

View answer
Setting Circular Replication in mysql
14 votes

Rolando told you how to do it and his answer is excellent. The truth is, circular replication is an awful, terrible, terrible idea. Don't do it. Ever. "Absolutes are never true," but in this case, ...

View answer
to what "scale" of applications does nosql support?
Accepted answer
8 votes

This question is really far too vague to answer effectively. There are dozens of "NoSQL" data stores out there which have various use cases. Here is a 10,000 foot view of what's out there. In my ...

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