How can the following SQL statement be optimized?

problem description

the following SQL statement exists: 

 SELECT `h1`.`oid`,`hr`.`uid`,`h1`.`bid`,`isself`,`tag`,h1.name as hname,
h1.add as hadd,
case when isself=1 then h2.name else h3.name end name ,
case when isself=1 then h2.add else h3.add end add
FROM `interv` `h1`
LEFT JOIN `relative` `hr` ON `h1`.`relative`=hr.relative and h1.oid=hr.oid
LEFT JOIN `self` `h2` ON `hr`.`uid`=`h2`.`uid`
LEFT JOIN `scrapy` `h3` ON `hr`.`uid`=`h3`.`uid`
WHERE  `h1`.`upon` = 1 
    and (case when isself=1 then h2.status else h3.status end)=2
    *and (case when isself=1 then h2.cid else h3.cid end)=2* 
order by h1.updatetime desc,
h1.createtime desc
limit 50

the environmental background of the problems and what methods you have tried

now I"m building a joint index for updatetime,createtime in H1, joint indexes for status and cid in tables of H2 and h3, and then indexes for status and cid separately. But now the execution efficiency is poor. The explain is as follows

wherecase when cid0.2s1s+

:



Mysql
Jun.19,2021
The

H1 table does not use an index, so the type type is index (full index scan)

give the following table structure

there is no direct relationship between H2 and h3. It is recommended to disassemble 2 SQL, to facilitate understanding 

SELECT *
FROM (
    (
        SELECT *
        FROM h1, hr, h2
        LIMIT 50
    )
    UNION
    (
        SELECT *
        FROM h1, hr, h3
        LIMIT 50
    )    
) a
ORDER BY a.time
LIMIT 50;

you say that you merge self and scrapy into one table, add more isself fields to distinguish them, and then set up a uid,cid,status joint index.
really needs to be separated. It is recommended that self and scrapy tables directly add a uid,cid,status joint index to have a try.

Previous: Electron-vue app is normal on ubuntu 14.04,18.04 prompts NavigatorUserMediaError.

Next: [JAVA rookie] an unknown error occurred when writing the interface for the first time.

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1eac046-46b22.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1eac046-46b22.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?