MySQLでビッグデータ（１億レコード以上のデータ）を作って遊ぼう
https://qiita.com/saitamasaitama/items/3ca05f8bd22044bfa26a

— posted by midilin @ 01:43PM | Comment (0)

PHPのスクリプトでも、まず「SET CHARACTER SET SJIS」を実行してから、SELECTを発行する

http://tod.cocolog-nifty.com/diary/2005/12/xamppphpmysql_6279.html

— posted by midilin @ 11:35PM

regxpを使う
http://dev.mysql.com/doc/refman/4.1/ja/regexp.html

半角英数字


like句の場所にregexp句を入れると正規表現マッチ

例）
「HOGE」列に数字で始まる10桁の英数を検索
select * from TEST where HOGE regexp '^[0-9]{1}[0-9a-zA-Z]{9}$'

http://guitarfish.wazure.jp/index.php?MySQL%E3%81%AESQL%E5%85%A5%E9%96%80#z3c7fe64

http://homepage2.nifty.com/copper29/ipr/mysql.html

— posted by midilin @ 06:46AM

unionを使うと、可能。

UNIONで初期値代入と加算処理を分けることで可能で、
適当なテーブルは、作成しようとしている擬似行以上の行が保証されているテーブルであればなんでもよい。

例）

SELECT @f:=1
UNION ALL
SELECT @f:=@f +1 FROM 適当なテーブル WHERE @f<8

http://ne.tc/2007/11/01/mysql%E3%81%AE%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E5%A4%89%E6%95%B0%E3%81%A7set%E3%82%92%E4%BD%BF%E3%82%8F%E3%81%9Asql%E4%B8%AD%E3%81%A7%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E5%A4%89%E6%95%B0%E3%81%AE/


もし、適当なテーブルが３項目取得が必要な場合

SELECT @f:=@f +1,項目1,項目2 FROM 適当なテーブル

unionでは、型をあわせないといけないので


SELECT @f:=1, null as 項目1, as 項目2
UNION ALL
SELECT @f:=@f +1,項目1,項目2 FROM 適当なテーブル

のようにしたら、うまくいった。

— posted by midilin @ 05:10PM

select sum(count) from
(
select count(`id`) as count from table1
union
select count(`id`) as count from table2
union
select count(`id`) as count from table3
) as union_data


mysqlの場合は、as 別名
をつけないとエラーがでるっぽい

— posted by midilin @ 06:30PM

●selectの高速化
１．テーブルを分ける。


２．テーブルにインデックスを設定する。
create index インデックス名 on テーブル名 (フィールド名);

文字列の場合はインデックスに設定する文字数を指定可能。
create index インデックス名 on テーブル名(フィールド名(要素数));


３．select count(*) よりも select(フィール名) を使う。
カウント処理もインデックスを設定してあるフィールド名を指定する事で高速化可能。


さらに、項目数が多い場合に、取得項目だけ取得するようなＳＱＬを別出しにして、
ＳＱＬを発行するとかなりレスポンスがアップした。


４．クエリキャッシュを使う。


クエリキャッシュが使える設定になっているのか確認するには、以下のようにする。
query_cache_sizeが0だと無効の状態。

mysql> show variables like 'query_cache_%e';

クエリキャッシュの利用状況を確認するには、以下のようにする。

mysql> show status like 'Qcache%';

Qcache_free_blocks：空きメモリブロック数

Qcache_free_memory：空きメモリ。

Qcache_hits：キャッシュにヒットしたクエリ数。

Qcache_inserts：キャッシュに保存したクエリ数。

Qcache_lowmem_prunes：メモリ不足で削除されたクエリ数。

Qcache_not_cached：キャッシュされなかったクエリ数。

Qcache_queries_in_cache：キャッシュに存在するクエリ数。

Qcache_total_blocks：合計ブロック数


●insertの高速化

１．まとめてINSERT


insert into word (Word_Id,Word_Data) values('1','abc');
insert into word (Word_Id,Word_Data) values('2','def');
insert into word (Word_Id,Word_Data) values('3','ghi');


ではなく、


insert into word (Word_Id,Word_Data) values('1','abc'),('2','def'),('3','ghi');



２．オートコミットをOFFにする。


http://www.cdl.im.dendai.ac.jp/~arai/study/mysql.html

— posted by midilin @ 10:44AM

UNION の結果セット内のカラムの型と長さでは、すべての SELECT ステートメントで取り出された値が考慮されます。 MySQL 4.1.1 より前のバージョンの UNION では、最初の SELECT で使用された値のみに基づいて結果の型と長さが決まる、という制約がありました。 この場合、たとえば、最初の SELECT の値よりも長い値が 2 番目の SELECT で取り出されると、切り捨てが行われることがあります。



(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;


mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| b |
+---------------+
MySQL 4.1.1 以降、この制約はなくなりました。


mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+


http://dev.mysql.com/doc/refman/4.1/ja/union.html




Mysql4.0で、対処する場合は、
最初にダミーテーブルをunionで作って、
文字列の長さを長くしておいて


SELECT REPEAT('a'100)
UNION
SELECT REPEAT('a',1)
UNION
SELECT REPEAT('b',10);


プログラム側で、一番最初のレコードを取得しないみたいな感じでやるとできた

— posted by midilin @ 03:23PM

http://www.pursue.ne.jp/jouhousyo/SQLDoc/select13.html

— posted by midilin @ 03:28PM

PHP＋Mysqlで1億行を超えるデータ量を持つデータベースや1テーブルで20Gバイトを超えるテーブルなど。


楽天のJavaとPHPの使い分けなど


http://itpro.nikkeibp.co.jp/members/SI/oss/20031007/1/
http://q.hatena.ne.jp/1184991094

— posted by midilin @ 03:37PM

1.LIKEを使う場合


１文字用のワイルドカード「_」（アンダーバー）を使い、対象月を「OR」で結ぶ「。
開始月、終了月によってSQLが変わってくる

［例］１月、２月をselectする場合
SELECT * FROM tablename
WHERE YMD lLIKE '____-01-__' OR YMD LIKE '____-02-__';


2.MySQLの関数を使う場合


$min_mm = 開始月;
$max_mm = 終了月; // 開始月 <= 終了月 であること
SELECT * FROM tablename
WHERE SUBSTRING(YMD,6,2) >= '$min_mm'
AND SUBSTRING(YMD,6,2) <= '$max_mm';


http://sb.xrea.com/archive/index.php/t-5546.html

— posted by midilin @ 12:00PM