photo: moriah sunrise
This Gentoo Wiki page suggests dumping the table and using iconv to convert the characters, then insert the dump into a new table with the new charset.
Alex King solved a different problem: his apps were talking UTF8, but his tables were Latin1. His solution was to dump the tables, change the charset info in the [...]
Google turned this up, but i have no idea how old it is: How to Monitor MySQL’s performance.
Now that I’m the nominal MySQL DBA for PSU, it became my job to jimmy up the MySQL user privileges so that the new web server could connect. I’m not sure if this is the fastest, most efficient way to do it, but it worked quickly enough:
CREATE TABLE mysql.user_copy SELECT * FROM mysql.user;
DELETE FROM mysql.user_copy [...]
When doing a bulk insert/update/change to a MySQL table you can temporarily disable index updates like this:
ALTER TABLE $tbl_name DISABLE KEYS
…do stuff…
ALTER TABLE $tbl_name ENABLE KEYS
From the docs:
ALTER TABLE … DISABLE KEYS tells MySQL to stop updating non-unique indexes. ALTER TABLE … ENABLE KEYS then should be used to re-create missing indexes. MySQL does this [...]
After an upgrade to MySQL 5.0.51b on RHEL 5 I started seeing curious results in a fairly common query. Here’s a simplified version:
SELECT ID, post_date_gmt
FROM wp_posts
GROUP BY ID
ORDER BY post_date_gmt DESC
LIMIT 5
What I expected was to get a handful of post ID numbers sorted in descending order by the post_date_gmt. Instead, I got [...]
GIS data seems to come in .shp (shape?) files, but it’s not like MySQL knows what to do with those. this MySQL forum post points to a PHP tool and Windows executable that promise to convert the .shp data into something more useful to MySQL.
Superfluo explains a little more, and there’s lots of .shp data [...]
MySQL offers ENVELOPE() to find the minimum bounding rectangle of a geometric object.
The result is a polygon with four segments, defined by five points. It took me a while to make sense of it, partially because the only documentation that I’ve run across so far for POLYGON() syntax is in the ENVELOPE() function mentioned above. [...]
It’s MySQL spatial data week here, though I am spreading out the posts to, um, ease the pain (or boredom). Anyway, here are some commands/functions I don’t want to forget about later:
Start with an existing table called geometry, add a spatial column and index it:
ALTER TABLE GEOMETRY ADD coord POINT NOT NULL;
CREATE SPATIAL INDEX coord [...]
MySQL has some powerful, and perhaps underused spatial extensions, but the most interesting functions are still unimplemented: “Note: Currently, MySQL does not implement these functions…”
Among those as-yet unimplemented functions is DISTANCE(). Alternatives can be found here and here, though neither is clean or simple. I wonder if a simple MBRContains() is good enough, though…
Found in the MySQL 5.0 Reference Manual:
Related(g1,g2,pattern_matrix)
Returns 1 or 0 to indicate whether the spatial relationship specified by pattern_matrix exists between g1 and g2. Returns –1 if the arguments are NULL. The pattern matrix is a string. Its specification will be noted here if this function is implemented.
(emphasis mine.)
