How to optimize a very slow query? I have this table structure. id is primary key:   

 id  post_id  email  telegram twitter  address  refer_link  referrer


Here is show create table:

CREATE TABLE `wpkx_airdrop_claim` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `post_id` bigint(20) NOT NULL,
 `email` varchar(100) NOT NULL,
 `telegram` varchar(60) NOT NULL,
 `twitter` varchar(60) NOT NULL,
 `facebook` varchar(250) NOT NULL,
 `discord` varchar(60) NOT NULL,
 `address` varchar(120) NOT NULL,
 `refer_link` varchar(8) NOT NULL,
 `referrer` varchar(8) NOT NULL,
 `ip` varchar(15) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `post_id_index` (`post_id`)
)


Table is now relatively big. When I run this query (it has around 9k rows) it takes more than 30 seconds to execute:

SELECT o.email
    ,telegram
    ,twitter
    ,address
    ,IFNULL((
            SELECT email
            FROM wpkx_airdrop_claim
            WHERE o.referrer = refer_link
            ), '') AS refer_email
    ,(
        SELECT count(referrer)
        FROM wpkx_airdrop_claim
        WHERE referrer = o.refer_link
        ) AS refer_count
FROM wpkx_airdrop_claim AS o
WHERE post_id = 8037
ORDER BY refer_count DESC


This query counts number of same values in referrer column, and group it with the value of refer_link column. It gets how many referrals user have.
It is explained in this post:
https://stackoverflow.com/questions/52538612/mysql-create-view-from-columns-from-same-table

Any idea how to optimize this query to execute it faster?

Thank you.
give me an example how to do it


Get something like

SELECT t1.email
     , t1.telegram
     , t1.twitter
     , t1.address
     , COALESCE(t2.email, '') AS refer_email
     , count(t3.referrer) AS refer_count
FROM wpkx_airdrop_claim AS t1
LEFT JOIN wpkx_airdrop_claim AS t2 ON t1.referrer = t2.refer_link
LEFT JOIN wpkx_airdrop_claim AS t3 ON t3.referrer = t1.refer_link
WHERE t1.post_id = 8037
GROUP BY t1.id
/* If ONLY_FULL_GROUP_BY, replace with
GROUP BY t1.email
       , t1.telegram
       , t1.twitter
       , t1.address
       , COALESCE(t2.email, '') AS refer_email
*/
ORDER BY refer_count DESC


And create indices by: (post_id, referrer, refer_link), (referrer) and (refer_link). Then test does they are used.