Archive for category sql

mysql, umlauts and collation. Hey, o is not = ö

I came to a problem where mysql thinks that ö=o. So if your tables collation ist utf8_general_ci for example and you do

SELECT * FROM table WHERE some_field LIKE '%ö%'

You will get every row containing an “ö” but you also get every row containing an “o” (and vice versa btw).So if you really want to select the right rows you can go like this:

SELECT * FROM table WHERE some_field LIKE ('%ö%'  COLLATE utf8_bin)

Now you only get the rows containing “ö”.

Leave a comment

Amazon EC2 to assist dedicated server?

We run a dedicated server at our work. As of the last posts about optimizing it for some special cases just another idea came to my mind:
How about letting an Amazon EC2 instance assist the dedicated server twice a year for just 2-3 hours?
Thats the only time it gets critical with the workload (mostly on the mysql database).
I yet dont know how, but if i get an idea on how this could be done i will post it here.
Maybe a replicated mysql instance, maybe a memcached instance on amazon? Would be quite cheap and could really help for just this little timeframe twice a year.

Leave a comment

Memcached vs. mysql query cache

And the winner is (in this case): memcached
On my site there is a course scheduler, and today i switched one simple query to fill memcached every 5 minutes.

Before this special site took around 0.09 seconds to load,
With memcached it takes 0.047 seconds. Thats almost half the time.
When filling memcached its 0.13 seconds, so is a bit slower, but most time this little change improves the site.
The query that now gets it boost from memcached is just a query that gets date and time for a specific course, so it would run around 100 times on that page.

As i use the query cache from mysql for both queries i can say, that even if memcached is running on the same machine, memcached won for this case.
In other cases the query cache may win.

, ,

Leave a comment

Found easy example for memcached with php and mysql

Just wanted to share that, and will try it next week when back at work:
Memcached example

Hope it gets me little performance boost, or at least less load on the server.

Leave a comment

PHP, SQL and sorting order

I searched for a good way to implement changing of sort order in php easily, but did not find a good way, so today i developed some handy code for sort order handling in PHP and want to share it. Maybe you find some downsides or maybe you like it.
You only have to configure the orderarray with your default sort order. The orderby parameter is filtered against that array, so there should be no danger of sql injection.

$orderarray=array(
  "Modul" => "name ASC",
  "Date"=>"date ASC",
  "Text" =>"description ASC");
if(!isset($_GET['orderby'])){
  $customorder=$orderarray;
}else{
  $customorder=array_intersect(explode(",",$_GET['orderby']),$orderarray);
}
$orderby=implode(", ", $customorder);
foreach($customorder as $key=>$value){
  $orderstring=implode(",", array_unique(array_merge(array($key=>$value), $customorder)));
  $orderlinks.='<a href="index.php?orderby='.$orderstring.'">'
             . array_search($value,$orderarray) .'</a>';
}
print $orderlinks;
$query="SELECT name, date, desciption FROM some_table ORDER BY $orderby";

Leave a comment

%d bloggers like this: