mysql - 如何让我的存储过程变得又快又漂亮...( select . select. select .. )

Question

我是初级 Ruby-mysql 程序员，我想知道如何使我的(存储过程)查询结果更快..

这是我的存储过程我正在使用 SQL_CACHE.. 但我不确定.. 缓存使我的过程更快.. : (

 DROP PROCEDURE IF EXISTS GET_AV //
    CREATE PROCEDURE GET_AV()
    BEGIN
        DECLARE OVERALL FLOAT ;
        DECLARE MALE FLOAT ;
        DECLARE FEMALE FLOAT ;
        DECLARE UNDER25 FLOAT ;
        DECLARE ABOVE25 FLOAT ;
        DECLARE UNDER25MALE FLOAT ;
        DECLARE UNDER25FEMALE FLOAT ;
        DECLARE ABOVE25MALE FLOAT ;
        DECLARE ABOVE25FEMALE FLOAT ;
        DECLARE CNT FLOAT;



    #AVERAGE OVERALL
    select SQL_CACHE  avg(r.frq) as AVG_OVERALL INTO OVERALL from tbl_survey as s inner join tbl_respondent as r on s.s_id = r.s_id;


    #AVERAGE MALE
    select SQL_CACHE  avg(r.frq) as AVG_MALE INTO MALE from tbl_survey as s inner join tbl_respondent as r on s.s_id = r.s_id WHERE GENDER = 1; 

    #AVERAGE FEMALE 
    select SQL_CACHE  avg(r.frq) as AVG_FEMALE INTO FEMALE from tbl_survey as s inner join tbl_respondent as r on s.s_id = r.s_id WHERE GENDER = 2;

    #AVERAGE UNDER25
    select SQL_CACHE  avg(r.frq) as AVG_UNDER_25 INTO UNDER25  from tbl_survey as s inner join tbl_respondent as r on s.s_id = r.s_id where AGE <= 25; 

    #AVERAGE ABOVE25
    select  avg(r.frq) as AVG_ABOVE_25 INTO ABOVE25 from tbl_survey as s inner join tbl_respondent as r on s.s_id = r.s_id where AGE >= 25; 


    #AVERAGE UNDER 25 & MALE
    select SQL_CACHE  avg(r.frq) as AVG_UNDER_25_MALE INTO UNDER25MALE from tbl_survey as s inner join tbl_respondent as r on s.s_id = r.s_id WHERE AGE <= 25 AND GENDER = 1; 

    #AVERAGE UNDER 25 & FEMALE
    select SQL_CACHE  avg(r.frq) as AVG_UNDER_25_FEMALE INTO UNDER25FEMALE from tbl_survey as s inner join tbl_respondent as r on s.s_id = r.s_id WHERE AGE <= 25 AND GENDER = 2; 

    #AVERAGE ABOVE25 & MALE
    select SQL_CACHE  avg(r.frq) as AVG_ABOVE_25_MALE INTO ABOVE25MALE from tbl_survey as s inner join tbl_respondent as r on s.s_id = r.s_id where AGE >=25 AND GENDER = 1; 

    #AVERAGE ABOVE25 & FEMALE
    select SQL_CACHE  avg(r.frq) as AVG_ABOVE_25_FEMALE INTO ABOVE25FEMALE from tbl_survey as s inner join tbl_respondent as r on s.s_id = r.s_id WHERE AGE >= 25 AND GENDER = 2;







    SELECT OVERALL,MALE,FEMALE,UNDER25 ,ABOVE25 , UNDER25MALE,UNDER25FEMALE ,
    ABOVE25MALE ,ABOVE25FEMALE;


    END //

幸运的是现在还不算太慢..但我想知道如何让它变得越来越快和漂亮的代码方式:(

Answer 1

BEGIN
SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; // These line helps to avoid locking table 

<Your select Query>

SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
END

//  As you are using same table number of times in the stored procedure
//  You can create temp table for that. This will avoid scan whole table from db.

CREATE TEMPORARY TABLE IF NOT EXISTS tmptbl_survey  ENGINE=MEMORY AS SELECT
  * FROM tbl_survey

// Use temp table in the query

//Then drop temp table
DROP TABLE tmptbl_survey 

尝试一下，祝你好运