WHAT TIME IS IT?

This post started with a simple question: “Does the function NOW() get executed every time a row is examined?” According to the manual,  “Functions that return the current date or time each are evaluated only once per query …. multiple references to a function such as NOW() … produce the same result. …. (However,) as of MySQL 5.0.12, SYSDATE() returns the time (the row is) executes. “

• CURDATE() returns the current date.
• CURTIME() returns the current time.
• UTC_DATE() returns the current UTC date.
• UTC_TIME() returns the current UTC time.
• NOW() return the current date and time.
• UTC_TIMESTAMP() returns the current UTC date and time.
• SYSDATE() returns the date and …

HandlerSocket is cool. But, it turns out there are a few issues.

Justin Swanhart points out HandlerSocket currently lacks atomic operations . Since HandlerSocket uses different connections for reading and writing, you can’t increment/decrement a value without creating a race condition.

Still, the idea of skipping SQL interpretation and just reading the data you know you want is a great one.  Writing data might even be better. But being able to use both SQL and NoSQL could be really wonderful.  What if we could use complex queries to update complex tables and pluck values out as needed.  For example, queries to analyze current weather conditions and produce forecasts that we could then retrieve via a location key? What about updating current condition data …

This document was updated and tested for CentOS 6.0

In my last two posts I installed the HandlerSocket plugin into MariaDB and showed how to use it with Perl.  That’s good, but if you are thinking of using HandlerSocket  I’m guessing you have a very high traffic website and it’s written in PHP.  In this post I’m going to connect HandlerSocket with PHP.  In the next post I’ll discuss using HandlerSocket on a production system.

There are a couple of HandlerSocket php modules projects.  I tried each of them and I found PHP-HandlerSocket was the best.  Both of them are still rough and neither of them have documentation beyond their source code.  Maybe this will move things forward.

Here are the applications you need to have installed that where not installed in my last two posts.  Run this to …

Is MySQL’s GIS really worth using?

Is GIS worth using in MySQL? In the past few post, I have explored what GIS is and how it is used. GIS encoded data is wonderful and can help with all kinds of cool queries.  I’m late getting this article written so lets get right to it.

The most common geographical  query is for all the point within some distance from a given point. I’ll try to focus on ways to answer this type of query. Accuracy of the answer is always important. Think carefully about your query. Do you want every pizza place within a radius of a port or within a square mile? Or, do you really want it within a miles walking distance?

I’m using the common city_lookup table for these tests. Here is the schema.

CREATE TABLE `city_lookup` (
`city_id` INT(7) NOT NULL DEFAULT '0',
`feature` VARCHAR(20) NULL DEFAULT NULL,
`name` VARCHAR(50) NULL DEFAULT NULL,
`pop_2000` INT(10) NULL DEFAULT …

Tweet

If your MySQL server has hundreds of clients (applications) and tens of thousands of queries per second,  MySQL default network settings may NOT be for you.  Network performance is not often a significant factor in the performance of MySQL.  That said, there are things to consider.

If you are building new applications make these changes now.  Developer expectations are hard to change.  My example below will break your application if  developers open a database connections and then spend ten minutes playing with their play doe before making a query.  If you have working applications …

I get ask, by application developers,  “how do you optimize MySQL”.  I do lots of things that don’t really relate to a developer. I analyze the percent of queries are being pulled from cache for instance.  What a developer can do to optimize the SQL they develop is a different questions.   So here is a quick list of things applications developers should know about MySQL.

Explain will analyze your query.

This example shows the possible indexes (keys) that could be used and the index that was selected.  2,262 rows where selected and then sorted (Using file sorts) and one record was returned (limit 1).

mysql> explain SELECT 5/9*(temp_F-32) as t, 5/9*(dewpt_F-32) as td, speed_mps as spd, dir
 > where stn='KLDM' and date_time<'2010-02-12 18:15' and …