MySQL: SUBSTRING_INDEX vs MID+INSTR (which is better)

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

MySQL | find median (Nth element) without join!

This is my suggestion to find on mysql select command

the Median (Nth element) without joining the table to itself (or to new custom one).

My method, uses string manipulation, 

so I'm not sure it is good for big tables.

(I tested it with medium size tables on Mysql 5.5.28)

The advantage of this method, 

that it works also if we need to find the MEDIAN for each group in the select query.

the code is using: SUBSTRING_INDEX and GROUP_CONCAT methods.


Here is test code for test table:

DROP TABLE test.test_median
CREATE TABLE test.test_median AS
SELECT 'book' AS grp, 4 AS val UNION ALL
SELECT 'book', 7 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 9 UNION ALL
SELECT 'book', 8 UNION ALL
SELECT 'book', 3 UNION ALL

SELECT 'note', 11 UNION ALL

SELECT 'bike', 22 UNION ALL
SELECT 'bike', 26 

and the code for finding the median for each group:

SELECT grp,
         SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val), ',', COUNT(*)/2 ), ',', -1) as the_median,
         GROUP_CONCAT(val ORDER BY val) as all_vals_for_debug
FROM test.test_median
GROUP BY grp

The output:

grp	the_median	all_vals_for_debug
bike	22	22,26
book	4	2,2,3,4,7,8,9
note	11	11

Hope it helps you there...