Hello there.
SUBSTRING_INDEX vs MID+INSTR
I wanted to pull from domain name out of an email (or just remove the username part of an email).
There are two simple functions (and even more...) to do it:
1] SUBSTRING_INDEX('testuser@domain.com','@',-1)
use built in function SUBSTRING_INDEX to explode the string by '@' and get the last element.
2] MID('testuser@domain.com', INSTR('testuser@domain.com','@')+1, 999)
find '@' position using INSTR and get all string from that position until the end of string by MID function.
But which one of them have better performance?
Using BENCHMARK function for Measuring the Speed of Expressions and Functions.
1] SELECT BENCHMARK(100000000, SUBSTRING_INDEX('testuser@domain.com','@',-1))
/* Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 12.625 sec. */
2] SELECT BENCHMARK(100000000, MID('testuser@domain.com', INSTR('testuser@domain.com','@')+1, 999))
/* Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 19.157 sec. */
BENCHMARK function results may vary between different devices, but on the same device can give quite clear results.
Summary:
SUBSTRING_INDEX won and have better results than MID + INSTR
