1,972 reputation
1614
bio website karwin.com
location California
age 46
visits member for 2 years, 8 months
seen 14 hours ago

I'm Senior Knowledge Manager at Percona, a services company specializing in the MySQL database.

I've been a software engineer since 1987, and my specialty is as an SQL maven. I also have experience programming in Java, PHP, Perl, C, JavaScript, and I have many other coding skills.

I've written a book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming from Pragmatic Bookshelf, based on the most common SQL problems I've answered on Stack Overflow and other forums, mailing lists, and newsgroups over the past 15 years.


14h
comment Fan-In replication 4 Master -> 1 slave
@Anil, MariaDB is based on MySQL 5.5, which is very mature. On the other hand, they add a lot of their own original code, which may have fresh bugs. Tungsten is much younger, but a lot of sites are using it successfully in production. Any assurance I give about maturity is meaningless, it only matters whether it works for you or not. And only you can test that.
1d
comment Fan-In replication 4 Master -> 1 slave
@Anil, All software has bugs.
1d
comment Fan-In replication 4 Master -> 1 slave
@Anil, you need at least 15 rep to upvote. stackoverflow.com/help/privileges/vote-up
2d
comment MySQL Slave not updated
@Ravi, there's always a mysql database, and usually a test database by default. There are also the system databases information_schema and performance_schema, but these are never replicated anyway.
Apr
10
comment MySQL silly query plan
With MySQL 5.6, they default to use persistent table stats, which don't get updated as often as we are used to in earlier versions of MySQL. So if you get crazy optimization, first try ANALYZE TABLE to force the stats to refresh. That fixes queries in many cases.
Mar
26
comment MySQL 5.1: Upgrade innodb plugin
If MySQL does not start, you should investigate the cause or specific error message in the MySQL error log. I did say that there was no guarantee that a plugin from another version would work. Why don't you want to just upgrade MySQL fully to 5.1.73?
Mar
25
comment Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)
Nope, the binary log records changes to any table, regardless of storage engine. How else would replication work?
Mar
25
comment Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)
@Hito_kun, you should read about point-in-time recovery with binary logs.
Mar
24
comment MySQL 5.1: Upgrade innodb plugin
mysql_upgrade doesn't upgrade the plugin. It just checks tables for compatibility with the new version, and adds new system tables or columns as needed in the mysql database.
Mar
14
comment what mistake i did in mysql query
Tilde is ~. The identifier delimiter is `, which we often call back-tick or back-quote. It's on the same key as the tilde (on a US keyboard), but without using SHIFT.
Mar
12
comment Dynamically change schema to accommodate custom fields!
Yes, the best implementation for custom schema extensions that I remember seeing is in Bugzilla, the old Perl bug-tracking app. You can add columns, and also add code in a separate directory to work with those columns.
Mar
10
comment Understanding MySQL's 'explain'
@MichałHerman, the type: index in your EXPLAIN describes how it's accessing the dd.data table. The primary key lookups (type: eq_ref) are on the tag tables. In a join, each table may respectively be accessed using a different join type.
Mar
10
comment How are auto_increment keys handled in INSERT (SELECT * FROM…)
Yes, SQL_MODE=NO_AUTO_VALUE_ON_ZERO, otherwise we could never insert a literal zero value into an AI column. If you use this SQL mode, NULL still works to activate the AI generator, or omitting the column works too.
Mar
5
comment New field based on sum field
You can keep nesting derived tables if you need to.
Feb
18
comment MyISAM with Innodb in the same Wordpress database reduce performace?
Yes, InnoDB supports ACID, data recovery, transactions, foreign keys, row-level locking -- none of which are supported by MyISAM. Also in modern versions of MySQL, InnoDB is faster and more scalable than MyISAM under most typical scenarios.
Feb
17
comment MyISAM with Innodb in the same Wordpress database reduce performace?
The downside is that some of your tables are still in MyISAM.
Feb
9
comment Disk write in iotop when doing mysql “Copy to tmp table”
You don't have to share the numbers with me -- you're the one who has to interpret them.
Jan
1
comment Is using multiple foreign keys separated by commas wrong, and if so, why?
@ErikHart, if that outsource team was given project specifications that included only one query against this data, they could have designed an optimization for that specific query only. In other words, "you asked for it, you got it." But the outsourcing provider has no reason to plan for future uses of the data -- they implement the application to the letter of what's written in the spec.
Jan
1
comment Is using multiple foreign keys separated by commas wrong, and if so, why?
@ErikHart, this is denormalization, and it can be helpful, but my point is that it depends entirely on the queries you need to run. You're right that denormalization makes all queries perform worse, except the one query it optimizes for. If you only need to run that one query, and you don't care about the other queries, it's a win. But these are rare cases, because typically we want flexibility to query the data in a variety of ways.
Dec
31
comment PostgreSQL list of integers separated by comma or integer array for performance?
+1 the only time a string with a comma-separated list is okay is when you always treat the list itself as an atomic data element. I.e. you never need to search for, sort by, or fetch individual elements using SQL semantics.