select
sum(case when todo.STATUS = 1 then 1 else 0 end) / count(*) * 100 as'TODO',
sum(case when todo.STATUS = 2 then 1 else 0 end) / count(*) * 100 as 'Doing',
sum(case when todo.STATUS = 3 then 1 else 0 end) / count(*) * 100 as 'Done'
from user join todo
on user.USER_ID = todo.USER_ID
where user.CREATED_AT >= DATE_ADD(NOW(), interval -2 month);
結果1 19.906秒
Todo
Doing
Done
33.3137
33.3160
33.3704
・クエリ2
SELECT todo.STATUS,
count(*) / (select count(*) from user join todo on user.USER_ID = todo.USER_ID where user.CREATED_AT >= DATE_ADD(NOW(), interval -2 month)) * 100 as '割合'
from user join todo
on user.USER_ID = todo.USER_ID
where user.CREATED_AT >= DATE_ADD(NOW(), interval -2 month)
group by todo.STATUS;
SELECT
table_name, engine, table_rows AS tbl_rows,
avg_row_length AS rlen,
floor((data_length+index_length)/1024/1024) AS all_mb, #総容量
floor((data_length)/1024/1024) AS data_mb, #データ容量
floor((index_length)/1024/1024) AS idx_mb #インデックス容量
FROM
information_schema.tables
WHERE
table_schema=database()
ORDER BY
(data_length+index_length) DESC;
data size
all_mbの合計値が必要量なので、330MB。
全然足りないので増やそう。
・バッファプールサイズの変更方法
350MBに増量。
SET GLOBAL innodb_buffer_pool_size = 350000000;
ちゃんと増えているか確認↓
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'
複合インデックス対象は、user テーブルは created_at + user_id 、 todo テーブルは user_id + status 。順番も変えて全部登録する。
alter table user add index idx_created_at_user_id(created_at, user_id)
alter table user add index idx_user_id_created_at(user_id, created_at)
alter table todo add index idx_status_user_id(status, user_id)
alter table todo add index idx_user_id_status( user_id, status)
public class A{
public static void main(String []args){
C c = newC("山田");
}
}
public class B{
public static void main(String []args){
C c = new C("山田");
}
}
public class C{
String name;
public C(String s){
this.name = s;
}
}
↓Factoryクラスを仲介するパターン
public class A{
public static void main(String []args){
Factory f = new Factory;
f.createClassB();
}
}
public class B{
public static void main(String []args){
Factory f = new Factory;
f.createClassB();
}
}
public class Factory extends C {
public void createClassC() {
C c = new C("山田");
}
}
public class C{
String name;
public C(String s){
this.name = s;
}
}
package adapter;
// Adapteeクラス
public interface Dvi {
public void shovMaker();
public void showOriginalType();
}
package adapter;
// Adapterクラス
public class DviToHdmiAdapter extends Hdmi implements Dvi{
DviToHdmiAdapter(String maker, String originalType) {
super(maker, originalType);
}
@Override
public void shovMaker() {
printMaker();
}
@Override
public void showOriginalType() {
printOriginalType();
}
}
package adapter;
// 実行クラス
public class Main {
public static void main(String[] args) {
Dvi dvi = new DviToHdmiAdapter("サンワ", "DVI");
dvi.shovMaker();
dvi.showOriginalType();
}
}
