Optimising a Query with 1 million rows

Lubricant Jam

I've been trying to optimise this query I've got, originally I was using INNER JOIN for the vip.tvip database however noticed that people that didn't exist in that table weren't showing and read I have to use a LEFT JOIN which has caused further issues.

SELECT sb_admins.srv_group AS role, rankme.lastconnect, rankme.steam, rankme.name, rankme.pfp, vip.tvip.vip_level FROM bans.sb_admins
INNER JOIN rankme ON CONCAT("STEAM_0:", rankme.authid) = sb_admins.authid
LEFT JOIN vip.tvip ON tvip.playerid = rankme.authid
AND gid > 0 ORDER BY rankme.name;

This is the query I'm currently using, it seems to take around 5 seconds to get the result due to the rankme table being 1.3 million rows. I am also attaching the EXPLAIN for this query too, I'm not that well versed in MySQL queries so apologies if I am butchering this.

If someone could give an in-sight on how to fix this, would be tremendously helpful. I have created keys for anything which I could such as name being a FULLTEXT key etc but still no prevail.

Cheers.

Luuk

Could you try:

SELECT sb_admins.srv_group AS role, rankme.lastconnect, rankme.steam, rankme.name, rankme.pfp, vip.tvip.vip_level FROM bans.sb_admins
INNER JOIN rankme ON rankme.authid = REPLACE(sb_admins.authid,"STEAM_0:","")
LEFT JOIN vip.tvip ON tvip.playerid = rankme.authid
AND gid > 0 ORDER BY rankme.name;

This should be able to use the index on rankme.authid in rankme. (if that exists...)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-11-22

Comments

0 comments

Prev: Laravel Migration: "ERROR Class not found"

TOP Ranking

Article

Optimising a Query with 1 million rows

Optimising a Query with 1 million rows

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Unable to use switch toggle for dark mode in material-ui

grouping by column variables and appending a new variable based on condition

BigQuery - concatenate ignoring NULL

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Python Read Directory And Output to CSV

Remove adjacent duplicates in linked list in C

Angular 8. Unknown amount of http.get requests in array to call, must be sequential, what to use

How to keep curl session alive between two php processes?

Limit number of characters in uitextview

JMeter: Why get error when try to save test plan

Always setting the text cursor on Textbox

MTKView Displaying Wide Gamut P3 Colorspace

Vector input in shiny R and then use it

How to implement an authentication method using Spring Boot and JPA?

Laravel getting value from another table using eloquent

org.springframework.web.client.HttpClientErrorException: 401 null

When I click any button in my view page the form is submitted