SET @sql:= (SELECT CONCAT(' SELECT item, rate, sum_rate, rnd FROM ( SELECT gacha_id, elt(n,',group_concat(CASE WHEN type=1 THEN col ELSE '' END SEPARATOR ''),') item, @rate:=elt(n,',group_concat(CASE WHEN type=2 THEN col ELSE '' END SEPARA…

フルスキャン前提でMySQLでランダムにサンプリングしたい場合 select * from テーブル名 where floor(rand()*100)>10 テーブルから10%のデータをサンプリングする場合socket handlerを使えばインデックスありでサンプリングできそうな気もする

リモートからのアクセスはmysqlからのアクセスを許可しておらず、phpMyAdminからしか許可していない場合にCLIでバッチ実行をしたい場合に便利。要pyton https://github.com/fdev/phpmyadmin-cli

以前のinsert_generate_seriesですと数万行のダミーデータ作成はできないので、以下のように改良しました。 DROP PROCEDURE insert_generate_series; delimiter [[ CREATE PROCEDURE insert_generate_series( IN target VARCHAR(255) , -- insert対象のテー…

プログラム中で、データベースからSELECT文を作るときに、テーブルのカラム数が多いと割とメンドクサイですよね。ぐぐっても出てこなかったので作りました。まず、指定したテーブルのカラム一覧を取得するシェルスクリプトを作ります。 getcolumn.sh #!/bin/…

MySQLでビックデータを扱う場合、データをサンプリングしたいことがある。MS-SQL Serverなどではサンプリング関数が用意されているが、MySQLにはない。 SELECT * FROM report ORDER BY RAND() LIMIT 5 と単純にクエリを書くこともできるが、ビックデータでこ…

毎度、ロジックを書くのは面倒ですので、 前回の内容をストアドファンクションにまとめます。 ltsv形式から指定キーのvalueを取得 DELIMITER // CREATE FUNCTION get_ltsv(str varchar(65535),`key` varchar(255)) RETURNS varchar(65535) DETERMINISTIC BEG…

値の読み込み(指定値 sample) SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( "test:27\tsample:8455\thogehoge:45", CONCAT('sample',':'),-1), "\t",1 ) ただ、これだと完全なるLTSVではないので、ちょっと考えないとまずいですね。例えば、key-valueが、test:…

前回の記事で、やりたいことを実現することはできましたが、indexが効かないので全然実用的ではありませんでした。 そこで別の方法を模索してみます。MySQLはユーザー変数を使った独自の集計(GROPU BY)を行うことでがきます。 ただし、MySQLのクエリー実行の…

MySQLのデフォルトのデータベースエンジンはビックデータの格納には向かない。特にALTERでカラムが頻繁に変更になる場合には、行数が多くなるほどALTERに時間が掛かる。なので、そのための用途として、DWHを使うのが一般的です。もしくはSpider+VPとかInfini…

今回は、BAD DB設計に対するSQLパターン入門編の応用編です。1カラムの中にカンマ区切りでデータが入っているBAD DB設計、これもわりと良く見るパターンですね。これを列に分解したい場合がある。簡単そうで実はわりと難しいテーマです。残念ながら、ぐぐっ…

CREATE TABLE `fruit` ( `fruit_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `fruit_name` VARCHAR( 255 ) NOT NULL ) ENGINE = INNODB; INSERT INTO fruit (fruit_id,fruit_name) VALUES (1,'いちご'), (2,'レモン'), (3,'メロン'), (4,'キューイ'), (5…

まずはサンプルのテーブルの定義 CREATE TABLE `item` ( `item_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `item_name` VARCHAR( 255 ) NOT NULL ) ENGINE = IONNODB ; INSERT INTO item (item_name) VALUES ('みかん'), ('リンゴ'), ('バナナ'), ('ト…

番外編として、こういったテスト用のサンプルデータを生成するのは骨が折れるので、例によってinsert_generate_seriesを使ってデータ作ります。 2012年01月のデータinsert_generate_seriesでランダムに作る call insert_generate_series( 'report(report_dat…

合計も同時に出したいだけであれば、GROUP BYと共にWITH ROLLUPを使うことでできます http://dev.mysql.com/doc/refman/4.1/ja/group-by-modifiers.html しかし、同時にカラムによって合計/平均を出したいなど複雑な条件の場合はできません。その場合ユーザ…

繰越処理 1でユーザー変数を使うとlimitやorder byを使ったソートの処理がめんどうになる。limitやorder byを使うことが想定される場合は、あらかじめ、paymentとcarry_overのカラムを追加しておいて CREATE TABLE `payment` ( `month_year` DATE NOT NULL ,…

ほぼ1年ぶりにMySQLをまともに使ったので、メモとして書いておきます。サンプルはAmazonのアフィリエイト報酬などに良くある最低支払い金額5000円未満の場合は翌月に繰り越されるという条件で、その月の収益から、その月の支払い金額、その月の繰越金額を表…

ご無沙汰しております。IT7Cです。MySQL*1でgeohashのUDFがなかったので自作してみました。間違い等あると思いますが、とりあえずソースを晒して寝ます。メモリーリークとかあるかもしれないので、本番環境に入れるのはやめて下さい。あとで、コードレビュー…

前回はbase64_encodeしか入れられなかったので、別のbase64のUDFのインストールを試みる。前回はgccのライブラリを使うものだったが、今回はphpのソースからの移植のようだ。 cd /usr/local/src wget http://bugs.mysql.com/file.php?id=3294 -O base64.c gc…

groonga ストレージエンジンはgroognaをストレージエンジンとして動作させるプラグインです。今までMySQLで日本語全文検索というと、Senna＋Tritonnという形でしたが、今後はSennaの後継 groogna+groogna ストレージエンジンがデファクトとなっていくのかも…

UDFの勉強を兼ねてshimariso さんが作成したUDF base64のMySQLに Base64エンコード関数が無くてムカついたから作ったこちらをインストールしてみる。 cd /usr/local/src git clone git://gist.github.com/361481.git cd 361481 g++ \ -I /usr/include/glib-2…

以下のORDER BY RAND()を使ったランダム取得は便利なのですが、インデックスが使用されず、フルスキャンになる為パフォーマンスが思わしくありません。 SELECT * FROM user ORDER BY RAND() LIMIT 1 いくつかのこの問題を解決するアプローチがありましたが、…

前々回のエントリー、HANDLER構文と同じくHANDLERを使ってストレージエンジンを直接操作するといったところは同じなのでこれをベースにして、handlersocket pluginではさらに低レベルの操作が可能になっています。またHANDLER構文と違って、参照系だけでなく…

DO構文 例えば、mycacheのポートを立ち上げる場合などで結果が必要ない場合などに便利です。 SELECT mycached_start(inet_aton('127.0.0.1'), 11211, 4) ↓ DO mycached_start(inet_aton('127.0.0.1'), 11211, 4) SLEEP SELECT SLEEP(10) ↓ DO SLEEP(10)

今回は、handlersocket pluginの元となるものといっても差し支えのないHANDLER構文について解説したいと思います。HANDLER構文はMySQLのストレージエンジンを直接操作するSQLです。1つのSQL文をMySQLに投げた時の内部処理*1を実行できるイメージです。具体的…

ソースのIPアドレスがうまく調整できないのでソースを見てみる。 mycache.cc 496行目 thread_t::start_server(unsigned host, unsigned short port, int num_threads) ↓intを追加 thread_t::start_server(unsigned int host, unsigned short port, int num_t…

CodezineのSQLをショートカットしてパフォーマンスを向上させるプラグインで紹介されていた樋口証氏の『MySQL handlersocket plugin』が面白そうだったので試してみたいと思っていたのですが、まだ、一般公開はされていません。※ 今後ディー・エヌ・エーの技…

MySQLの原作者、MontyさんことMichael Widenius氏がMySQLよりフォークして作ったMariaDBを入れてみる。みなさんもご存知の通りMySQL(Sun)がOracleに買収されて、MySQLの先行きが不透明な状況です。そのような状況の中、原作者のMontyさんがMySQLよりフォーク…

MySQL ソースからbuild時にconfigureの一部オプションが認識されなくなる問題 5.1.31以降から5.1.45まで続いている。原因はautoconfのバージョンアップに伴なうもの。

cat /usr/local/mysql/run #!/bin/sh ulimit -n 8192 exec \ setuidgid mysql \ env - PATH="/usr/local/mysql/bin" \ mysqld --open-files-limit=8192 --max_connections=1000 \ 2>&1 cat /usr/local/mysql/log/run #!/bin/sh exec \ setuidgid mysql \ mul…