1

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.

  • explain <your query> is something to be run and the output copied to the question. It should be done for every query performance problem. It isn't a conceptual request for a better explanation (though that often helps). – danblack Jan 25 '19 at 0:15
  • I hope it is still InnoDB? – Rick James Jan 26 '19 at 17:32
1

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.

|improve this answer|||||
  • Thank you for the help. I have tried this query. It actually runs slower. It takes 42 sec to execute, against 31 sec previously. – Ante Medic Jan 25 '19 at 8:37
0
WHERE post_id = 8037

begs for INDEX(post_id) ! That will speed it up by a few orders of magnitude.

And INDEX(referrer)

And INDEX(refer_link, email)

9K rows is "small".

Indexes are often the solution to performance of queries. More: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Index summary:

Currently:

PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `post_id_index` (`post_id`)

Recommended

PRIMARY KEY (`id`),
KEY `post_id_index` (`post_id`),
INDEX(referrer),
INDEX(refer_link, email)

Note that the PK is a UNIQUE KEY, so your second index on id is redundant.

|improve this answer|||||
  • Hello. I have created an index for post_id. There is no performance improvement. Should I also try adding other indexes you have suggested? Only referal_link column is unique. Thanks. – Ante Medic Jan 25 '19 at 8:43
  • @AnteMedic - The CREATE TABLE you provided does not show that index?? More than that, there is no referal_link???? A UNIQUE is already an INDEX, so no need for that one. Yes to the other one. If there are still issues, pleas add an updated SHOW CREATE TABLE to your Question so we can look for other issues. – Rick James Jan 25 '19 at 18:48
  • Sorry, column is is refer_link. I have updated the show create table. Should I also ad your suggested indexes? Thanks. – Ante Medic Jan 25 '19 at 22:58
  • @AnteMedic - Yes, see my update. – Rick James Jan 26 '19 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.