So this is about a SELECT COUNT(*) FROM tblname without a WHERE clause. MyISAM has an optimisation for that since it maintains a rowcount for each table. InnoDB and PBXT can’t do that (at least not easily) because of their multi-versioned nature… different transactions may see a different number of rows for the table table! [...]
What’s the difference between INT(2) and INT(20) ? Not a lot. It’s about output formatting, which you’ll never encounter when talking with the server through an API (like you do from most app languages). The confusion stems from the fact that with CHAR(n) and VARCHAR(n), the (n) signifies the length or maximum length of that [...]
We don’t often see this option configured (default: unlimited) but it might be a good idea to set it. What it does is limit the amount of disk space the combined relay logs are allowed to take up. A slave’s IO_Thread reads from the master and puts the events into the relay log; the slave’s [...]
Some guidelines to choosing between MySQL’s numeric types, using longitude and latitude as a modelling example.
We got good responses to the “identify this query profile” question. Indeed it indicates an SQL injection attack. Obviously a code problem, but you must also think about “what can we do right now to stop this”. See the responses and my last note on it below the original post. Got a new one for [...]
You do SHOW PROCESSLIST, and you see one of your web apps issue the following query: SELECT … WHERE … AND 1=2 UNION SELECT … What does this tell you, and what do you do next?
This is a “dogfood” type story (see below for explanation of the term)… Open Query has ideas on resilient architecture which it teaches (training) and recommends (consulting, support) to clients and the general public (blog, conferences, user group talks). Like many other businesses, when we first started we set up our infrastructure quickly and on [...]
Do you know if your production MySQL servers will come back up when restarted? A recent support episode illustrates a number of best practices.
In today’s gp/bp an open door will be kicked in: take your backups offsite! I was actually tempted to create a poll to see how many of you do not have proper backups, and how many of you do not take those backups offsite. It is a simple piece of advice and relatively simple to [...]
When you write your create table statements, always make sure that you make them non-ambiguous. That way even though other servers might have different configurations, you make sure your table will be created in the same way. Imagine for instance you are developing an application on a development server, nicely storing all the scripts you [...]