Inline if-then-else in MySQL queries
It can be useful to have a Ruby expression like condition ? positive_case : negative_case in MySQL queries:
UPDATE users SET monthly_debit = IF(subscriber, 19, 0)
Related cards:
MySQL: Can I speed up LIKE queries by adding an index?
For string columns, MySQL indexes the left side of a string. That means an index can speed a like query that has a wildcard on the right side:
SELECT * FROM foo WHERE field LIKE "bar%" # will be faster with an index
It can not speed ...
Performance analysis of MySQL's FULLTEXT indexes and LIKE queries for full text search
When searching for text in a MySQL table, you have two choices:
- The LIKE operator
- [FULLTEXT](http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html...
MySQL: Do not use "WHERE id IN (SELECT ....)"
Note: This applies specifically to MySQL. In PostgreSQL for example, this is not an issue.
If you care about performance, never use a query like
UPDATE users SET has_message = 1 WHERE users.id IN (SELECT user_id FROM messages)
MySQL doe...
MySQL will not use indexes if you query the wrong data type
When MySQL refuses to use your index, there's a number of things that you may be doing wrong. One of them might be conditions with improper data types.
An example
For example, let's assume you have a users table with an email field (`var...
How to turn images into inline attachments in emails
Not all email clients support external images in all situations, e.g. an image within a link. In some cases, a viable workaround is to turn your images into inline attachments.
Note
Rails provides a simple mechanism to achieve this:
- [h...
Timeouts for long-running SQL queries
While the main goal always is to prevent long-running queries in the first place, automatic timeouts can serve as a safety net to terminate problematic queries automatically if a set time limit is exceeded. This prevents single queries from taking...
Understanding SQL compatibility modes in MySQL and MariaDB
MySQL and MariaDB have an SQL mode setting which changes how MySQL behaves.
The SQL mode value is comprised of multiple flags like "STRICT_TRANS_TABLES, NO_ZERO_IN_DATE". Each flag activa...
How Rails and MySQL are handling time zones
When working with times and dates in Rails applications, you need to deal with the following problem:
- In Rails,
Timeobjects have a time zone. You can get the zone name by doingtime_object.zone. - This zone is considered when doing time ca...
MySQL replication how-to
This may be awkward to set up, but will work once you're done.
Fun facts:
- In case of a connection loss the slave will try to reconnect to the master server and resume replication for the next 24 hours
- If you want to use your slave as a "real...
Simple database lock for MySQL
Note: For PostgreSQL you should use advisory locks. For MySQL we still recommend the solution in this card.
If you need to synchronize multiple rails...