Reputation
14,073
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
2 20 43
Newest
 Talkative
Impact
~1.1m people reached

Feb
20
comment Why does MySQL allow HAVING to use SELECT aliases?
@Pacerier MySQL is "doing pre-analysis," of course, because the query optimizer considers all facets of the query while choosing what it believes will be the best query plan. The notion of a "different conceptual interpretation" betrays a misunderstanding of the fact that the server is free to implement the conceptual model in any way that produces a valid result. ORDER BY, for example, might be actually handled much earlier than it theoretically is, if the optimizer finds that rows can be initially read in order from an index that's already in the desired order.
Feb
18
comment Integer ID vs varchar ID with JOIN
Far too small. This is one of the very few tunable parameters that ever needs to be changed, but it's a very important one. Disk access is going to be killing your performance with only a 16MiB buffer pool. Configure it for half the machine's RAM, restart MySQL service and then check performance, remembering that the pool has to warm up, so the first queries will be the slowest after restart.
Feb
17
comment Integer ID vs varchar ID with JOIN
I believe a comment on the question itself may notify all of the answerers, but I am not certain. Your examples do show a significant performance decrease from the join, far more significant than should be expected. This needs investigation. SELECT @@innodb_buffer_pool_size; Also, index length 0 on the one table because the primary key index is where the row data is actually stored in InnoDB, so this would all be in data_length.
Feb
15
comment How do I stop tables from being created in my mysql database?
I downvoted this answer for being inaccurate as well as for not addressing the overriding issue that this appears to be a compromised system and poking at symptoms is not an appropriate course. I stand by the downvote and recommend against using terms like "pathetic" and "ego" to describe justifiable behavior by established members of this community. You can potentially make the downvote go away by improving the answer with a substantial edit (my vote is locked until you do)... or deleting it.
Feb
14
answered Integer ID vs varchar ID with JOIN
Feb
14
comment Execution plan not understanding correctly
Can mysql read an index partially backwards in this condition? Does ORDER BY fr_user DESC, created DESC change the query plan?
Feb
5
comment How do I stop tables from being created in my mysql database?
Sorry, that's incorrect. There is no special privilege accorded of necessity to any user that happens to be named "root" in MySQL. The SUPER privilege is what grants superuser status to accounts.
Feb
1
comment Tuning MySQL variables to accommodate high load
You almost certainly have too many parameters that are not using default values. Bigger is not always better. See also dba.stackexchange.com/a/45570/11651 for my thoughts and other info on "tuning" your server's performance right into the ground, and a great pull-quote from the book High Performance MySQL about tuning scripts and performance tuning, where the bottom line, according to the authors (with whom I agree but am not affiliated), is this: “Tuning” your server can be a stunning waste of time.
Jan
29
awarded  Mortarboard
Jan
28
comment Slave Waiting to reconnect
Didn't you say you had a power outage? If the master server lost power there isn't much you can do, but it's also possible that you have a physical disk issue.
Jan
28
comment Slave Waiting to reconnect
yep, looks like corrupt logs.
Jan
28
comment Slave Waiting to reconnect
Okay, just making sure... you didn't show the error output, so I wasn't clear if you'd had a genuine error or just the error that would have been generated if you had used that as-is with no log 000004.
Jan
28
comment Idempotent user and database creation script with MySQL
No, my intention was not that GRANT USAGE would provide a blank slate... GRANT USAGE is a no-op on existing users, so my intention was that you could use that first, then DROP USER would not fail, because you'd then always have something to drop. Your shortcut runs the risk of the user having old/unexpected permissions.
Jan
27
comment Slave Waiting to reconnect
I thought that command pipeline looked familiar. :) Note that you need to substitute mysql-bin.000004 with the actual binlog file you're trying to read.
Jan
27
answered Idempotent user and database creation script with MySQL
Jan
20
comment How to clean up tables that don't belong into mysql/mysql database?
@donquixote you could also grep for them in your most recent backup file from before the mistake was made.
Jan
13
awarded  Good Answer
Jan
13
comment AWS Webapp tutorial: how to choose source in database security group?
Start typing sg- in the box for source IP address. Does anything pop up?
Jan
12
answered Slave stopped : Could not parse relay log event entry
Jan
12
comment Slave stopped : Could not parse relay log event entry
The advice from @NawazSohail is not appropriate for the observed conditions, and should not be followed here. You can't blissfully skip over an event of unknown content without consequences, which may not become evident until later.