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 | join/combine 2 SMALLINT/TINYINT into 1 INT

Hi All.

Today we will try to combine two MYSQL small integer values into one integer value.
Obviously, there is limit for the size of integer.
We will discuss only UNSIGNED integer column types (non negative values).

2 Solutions are suggested below:
Solution-1: concatenate base-9 value of each number with '9' separator.
Solution-2: concatenate the original values with padding (left) zeros.
[Note: if the values allowed to be NULL, solution-2 is not supported!].

# LIMITS #

First, lets learn about the range limits of each data-type:

Datatype	Type	UNSIGNED Range	Max value (base9)
INT	A normal-size integer	0 to 4294967295	-
SMALLINT	A small integer	0 to 65535	CONV(65535, 10, 9) = 108806
TINYINT	A very small integer	0 to 255	CONV(255, 10, 9) = 313

# SOLUTION-1 (Support NULL values) #

Combine and merge two small integers,
into 1 integer by converting each number to base-9 integer,
and then concatenate with the digit '9' as separator.

Two SMALLINTs cannot be saved inside INT value:
1088069108806 > 4294967295.
But combining SMALLINT with TINYINT is possible (and aswell two TINYINT).

MYSQL CODE:

############ TABLE DEFINITION:
CREATE TABLE `table_test` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`sint1` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`tint2` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE);

############ SELECT:
SELECT  T.*, 
        CONV(SUBSTRING_INDEX(combined,'9',1), 9,10) as _sint1, 
        CONV(SUBSTRING_INDEX(combined,'9',-1),9,10) as _tint2
FROM (
	SELECT 	id, sint1, tint2, 
                CONCAT( IFNULL(CONV(sint1, 10, 9),''), 
                        '9', 
                        IFNULL(CONV(tint2, 10, 9),'')
                ) AS combined
	FROM table_test
) AS T

            

And output will be:

id	sint1	tint2	>combined	>_sint1	>_tint2
1	NULL	NULL	9	NULL	NULL
2	1234	NULL	16219	1234	NULL
3	NULL	255	9313	NULL	255
4	3123	38	4250942	3123	38
5	3410	243	46089300	3410	243
6	2685	207	36139250	2685	207
7	1327	224	17349268	1327	224
8	2047	102	27249123	2047	102
9	2953	189	40419230	2953	189
10	3686	117	50459140	3686	117
11	4455	18	6100920	4455	18
12	2482	66	3357973	2482	66
13	3072	74	4183982	3072	74
14	2036	42	2712946	2036	42
15	2047	139	27249164	2047	139
16	1573	236	21379282	1573	236
17	2464	173	33379212	2464	173
18	3656	155	50129182	3656	155
19	3305	120	44729143	3305	120
20	5894	121	80689144	5894	121

# SOLUTION-2 (NULLS not supported) #

Here we just merge the original values with LEFT-PADDING of zeros.

MYSQL CODE:

############ TABLE DEFINITION:
CREATE TABLE `table_test` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`sint1` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`tint2` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE);

############ SELECT:
SELECT  T.*, 
        SUBSTR(combined,1,LENGTH(combined)-3)+0 AS _sint1,
        RIGHT(combined,3)+0 AS _tint2
FROM (
	SELECT 	id, sint1, tint2, 
                CONCAT(sint1, LPAD(tint2,3,0)) AS combined
	FROM table_test
) AS T
            

And output will be:

id	sint1	tint2	>combined	>_sint1	>_tint2
4	3123	38	3123038	3123	38
5	3410	243	3410243	3410	243
6	2685	207	2685207	2685	207
7	1327	224	1327224	1327	224
8	2047	102	2047102	2047	102
9	2953	189	2953189	2953	189
10	3686	117	3686117	3686	117
11	4455	18	4455018	4455	18
12	2482	66	2482066	2482	66
13	3072	74	3072074	3072	74
14	2036	42	2036042	2036	42
15	2047	139	2047139	2047	139
16	1573	236	1573236	1573	236
17	2464	173	2464173	2464	173
18	3656	155	3656155	3656	155
19	3305	120	3305120	3305	120
20	5894	121	5894121	5894	121

This solution can work with two SMALLINT,
if one of them limited up to 42949,
and it will be the left operand.

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...

MySQL xpath ExtractValue with delimiter

Hello,

I recently noticed the XPATH option inside MySQL.

What a wonderfull way for handling XML. (http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html)

I used ExtractFunction, and noticed that this function is space delimited:

Because ExtractValue() returns multiple matches as a single space-delimited string

So I needed to create the same function with custom text delimted (comma, semi-colon or anything you want).

The function called: EXTRACTVALUE_ALL which is the same as EXTRACTVALUE just get a delimiter text to seperate between results. (seperated text).
Tested on MySQL 5.5.28.

 

Create Function CODE:

DROP FUNCTION IF EXISTS EXTRACTVALUE_ALL;


DELIMITER |
CREATE FUNCTION EXTRACTVALUE_ALL(p_xml TEXT, p_xpathExpr TEXT, p_delimiter TEXT) RETURNS TEXT
BEGIN
DECLARE total_elements INT;
DECLARE xpath_expression_count, xpath_expression_index  TEXT;
DECLARE single_tag, result  TEXT;

# calc TOTAL elements
SET xpath_expression_count = CONCAT('count(', p_xpathExpr, ')');
SELECT EXTRACTVALUE(p_xml, xpath_expression_count) INTO total_elements;

# run over elements (create long text)
SET result = '';
SET xpath_expression_index = CONCAT(p_xpathExpr, '[$@i]');
SET @i = 1;
WHILE @i <= total_elements DO
SET single_tag = EXTRACTVALUE(p_xml, xpath_expression_index);
SET result = IF(result='', single_tag, CONCAT(result, p_delimiter, single_tag));
SET @i = @i + 1;
END WHILE;

# return total result
RETURN result;
END |

Usage Code:

SET @XXXMMMMLLL = '  time1   time2 ';

SELECT EXTRACTVALUE_ALL(@XXXMMMMLLL, '/descendant-or-self::time', '  |  ');

Output:

time1  |  time2

Rememeber that you will need a MYSQL permission for creating new functions.

MDB-BLOG :)