3,067 reputation
1720
bio website karwin.com
location California
age 47
visits member for 3 years
seen 9 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.


1d
comment MySQL CPU & Memory Spikes
CPU spikes in MySQL are not uncommon, depending on the version. It could be a variety of causes. To be sure, you'd want to use oprofile or something to find out exactly which functions within the mysqld process account for the most execution time during the CPU spikes. My first guess is the query cache, which has been known to spend time resorting itself internally - try setting query_cache_type=0 and query_cache_size=0 and restart. See if that helps.
Aug
27
comment Proposal: MySQL blob handling revision
@IGotRoot, how would your idea support transaction isolation, transaction rollback, redo logging, or crash recovery? You should learn more about what databases do, that makes a database not the same as a filesystem.
Aug
25
comment How to change rows to columns in mysql?
It's hard to debug an SQL query by staring at the code that generates the query. I suggest you SELECT @sql to return the final query before you try to prepare it. The syntax error may be more clear if you look at the full query.
Aug
15
comment Why is MySQL ignoring my indexes?
One should change IN (1) to = 1 to make those predicates be ref instead of range. Unless MySQL can optimize a 1-element list into a ref, but IIRC it can't do that.
Aug
15
comment mysql - is it possible to create a partition expression based on two keys / indexes?
Both answers below are good, but let's be clear: the answer is no, you cannot partition a single table by two different keys. Can I separate my jellybeans by color and also by size? No -- at best you can do subpartitioning for the second condition. But that won't allow you to search only one partition when you search for jellybeans by size.
Jul
25
comment Is my MySQL instance irretrievably dead?
Looks like you installed MySQL 5.1 over a 5.0 (or older) installation, without running mysql_upgrade to create new system tables and add columns to existing system tables.
Jul
15
comment Percona MySQL 5.5 Unique key is duplicated
To be fair, the market deserves some blame too, for creating the priority on performance over correctness.
Jul
15
comment Converting to MyISAM, `foreign_key_checks = 0` Not Working
@ypercube, thanks, I have posted an answer to that question.
Jul
2
comment How to transfer 70GB MySQL with no downtime?
Another option for quick backups of MyISAM databases is LVM snapshots. A good tool to help is mylvmbackup. But your data directory needs to be on a volume capable of snapshots already.
Jun
20
comment Database Normalization - Can one field point to another?
As ypercube and mustaccio mentioned, a physical locator to the row is subject to change, as InnoDB moves rows around during updates. Besides, a foreign key typically references the primary key of the parent table, which is the clustered index of that table, and therefore pretty quick for lookups.
Jun
20
comment Database Normalization - Can one field point to another?
And how large would such a pointer need to be, given that it would have to identify the tablespace, the page, the row, and column it references? Hint: almost certainly larger than the value you're likely to be referencing.
Jun
18
comment mysql replication missed quite a lot SQL statement
Keep in mind that replicate-ignore-db causes statements to be skipped if the current default db was ignored. So if you use mysql then insert into otherdb.sometable then that insert will be skipped.
Jun
12
comment Cannot add foreign key constraint while creating table
@Lennart, in standard SQL, you have to use the whole key and it must be a unique or primary key. InnoDB is more permissive, allowing the referenced index to be non-unique, or allowing the referenced columns to be the left-prefix subset of an index. That's non-standard behavior. I don't recommend doing that, because you get into weird situations like a child row that references multiple rows in the parent. Down that path lies only madness!
Jun
12
comment Cannot add foreign key constraint while creating table
I tried your case in MySQL 5.6 and it worked fine, but I had to create also tables Movie and Rent_Status. InnoDB does support NO ACTION syntax. Note that the column referenced by a foreign key must be the leftmost column in a key in its table.
Jun
8
comment Amount of data in a database table
This is tangential to your question, but in most cases a FLOAT or DOUBLE column should be DECIMAL instead.
Jun
4
comment Only “grant usage”, but can still select, drop, create?
FWIW, this is fixed in Percona Server. I.e. we just don't add those rows in our distribution.
Jun
3
comment MySQL crashes frequently
Sizing the buffer pool as a percentage of RAM assumes a certain typical range for the size of RAM. Don't take it as a law, it's a guideline for the typical case. You have a non-typical case, i.e. not enough RAM.
May
30
comment How are auto_increment keys handled in INSERT (SELECT * FROM…)
Clever, but to me, that counts as spelling out the full list of column names you want to insert.
May
29
comment MySQL incremental backups per database (not global)
Yeah, like any tool, it will probably take you a while to experiment with it to figure out if it meets your needs.
May
25
comment Partitioning large mysql table
Yes, I'd monitor INFORMATION_SCHEMA.PARTITIONS to see how many rows are in the latter partitions.