一次非常有意思的 SQL 优化经历

create table Course(

c_id t PRIMARY KEY,

name varchar(10)

)

create table Student(

id t PRIMARY KEY,name varchar(10)
)

CREATE table SC(
    sc_id t PRIMARY KEY,
    s_id t,
    c_id t,
    score t )

select s. from Student s  where s.s_id  (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

EXPLAIN select s. from Student s where s.s_id  (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

CREATE dex sc_c_id_dex on SC(c_id);

CREATE dex sc_score_dex on SC(score);

SELECT    `YSB`.`s`.`s_id` AS `s_id`, 
   `YSB`.`s`.`name` AS `name`FROM    `YSB`.`Student` `s` WHERE     < _optimizer > (
        `YSB`.`s`.`s_id` ,< EXISTS > (
            SELECT                 1             FROM                 `YSB`.`SC` `sc`
            WHERE                (
                    (`YSB`.`sc`.`c_id` = 0)
                    AND (`YSB`.`sc`.`score` = 100)
                    AND (
                        < CACHE > (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`
                    )
                )
        )
    )

select s_id from SC sc where sc.c_id = 0 and sc.score = 100

select s. from Student s where s.s_id (7,29,5000)

SELECT s. from 

Student s INNER JOIN SC sc on sc.s_id = s.s_id where sc.c_id=0 and sc.score=100

SELECT     `YSB`.`s`.`s_id` AS `s_id`,
    `YSB`.`s`.`name` AS `name`FROM     `YSB`.`Student` `s`JOIN `YSB`.`SC` `sc` WHERE     (
        (
            `YSB`.`sc`.`s_id` = `YSB`.`s`.`s_id`
        )
        AND (`YSB`.`sc`.`score` = 100)
        AND (`YSB`.`sc`.`c_id` = 0)
    )