Ask The Real Tom

July 30, 2010



MySQL solution: Speed up pattern searches starting with a wildchar (like ‘%abc%’)

Filed under: Uncategorized — Tags: , , , , , , , , — admin @ 10:04 am

Searches on MySQL with expression like ” … like ‘%abc%'” can be incredibly slow on large tables.
The problem is, MySQL can not use indices for expression starting with a wildcard “%”.
Neither MySQL supports function based indexes.

I will explain a simple solution. But first lets have a look to our problem.
Table size: 2G
Row count: 33’000
Query response time: ~ 25s

mysql> desc images;
+-------------+-----------------------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------+------+-----+---------+----------------+
| imageid | int(11) | NO | PRI | NULL | auto_increment |
| filename | varchar(200) | YES | MUL | NULL | |
| description | varchar(200) | YES | | NULL | |
| data | longblob | YES | | NULL | |
| type | enum('coversmall','cover','coverback','screenshot','specialcover','banner') | YES | MUL | NULL | |
+-------------+-----------------------------------------------------------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

The index can not be used


mysql> explain select imageid,filename,description,type from images where filename like '%call%' order by filename;
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | images | ALL | NULL | NULL | NULL | NULL | 371398 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

Index can be use if the expression does not start with a wild card


mysql> explain select imageid,filename,description,type from images where filename like 'call%' order by filename;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| 1 | SIMPLE | images | range | idx_images_filename | idx_images_filename | 603 | NULL | 66 | Using where |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select imageid,filename,description,type from images where filename like 'call%abc' order by filename;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+
| 1 | SIMPLE | images | range | idx_images_filename | idx_images_filename | 603 | NULL | 66 | Using where |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+------+-------------+

Since we are searching for the filename, we a new column for the filename. Add a character in front of the string and index the new column.


mysql> alter table images add vfilename varchar(201);

Query OK, 33064 rows affected (3 min 4.73 sec)
Records: 33064 Duplicates: 0 Warnings: 0

update images set vfilename=concat('a',filename);

Query OK, 33064 rows affected (52.40 sec)
Rows matched: 33064 Changed: 33064 Warnings: 0

create index idx_images_vfilename on images (vfilename);

Now we change our application so it add always the same character “a” in the pattern.
So we can use the index.


mysql> explain select imageid,filename,description,type from images where vfilename like 'a%abc%' order by filename;
+----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | images | range | idx_images_vfilename | idx_images_vfilename | 603 | NULL | 75840 | Using where; Using filesort |
+----+-------------+--------+-------+----------------------+----------------------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

The perfomance benefit is, that we can do now a index scan (full/range scan) which read much less data than a full table scan.
We reduced i/o, saved our cache and speed up the query by magnitudes!

This is a real ask the real tom solution!

February 19, 2010



MySQL Dynamic DDL in Prozedure or Function

Filed under: mysql — Tags: , , , — admin @ 10:16 am

Yesterday I ran into a challenge on mysql.

A procedure to create an drop a database on mysql has been needed. My first thought was “easy”, it wasn’t.

There is no “execute immediate” or dynamic variable replacement in mysql. The workaround to execute dynamic DDL in a mysql procedure was found in a bug description. (http://bugs.mysql.com/bug.php?id=9150)
It can be solved with a prepared statement.


set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;

My template to create a DB I just poste here. Maybe it helps:


delimiter //

DROP PROCEDURE IF EXISTS create_schema;
CREATE PROCEDURE create_schema (p_schema_name VARCHAR(50))
SQL SECURITY DEFINER
BEGIN
DECLARE v_schema_count INTEGER;
startb:BEGIN

-- check exists
SELECT count(1) INTO v_schema_count FROM information_schema.SCHEMATA WHERE upper(SCHEMA_NAME)=upper(p_schema_name);
IF v_schema_count > 0 THEN
SELECT 'Error: DB Exists' FROM dual;
leave startb;
END IF;
-- check name
IF ( INSTR(p_schema_name ,'AD_') = 0) THEN
SELECT 'Error: Invalid schema name' FROM dual;
leave startb;
END IF;
-- create db
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;

-- grant permissiosn
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;

END startb;
END;
//

April 1, 2009



Fast import/export in MySQL with LOAD DATA INFILE

Filed under: mysql — Tags: , , , , — admin @ 3:05 pm

MySQL does not offer many possibilities for importing/exporting data in a fast manner. One way to do so is to use the LOAD DATA INFILE statement for loading data from/to text files.

SELECT * INTO OUTFILE '/tmp/data.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; FROM test.data_table;

With the example above you create a csv (comma-separated values) file with the content of a given table.

To read rows from a text file into a table use the following example:

LOAD DATA INFILE '/tmp/data.txt' INTO TABLE data_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Furthermore this way you are able to parallelize imports and exports to reduce the runtime. Just execute several statements simultaneously.

Powered by WordPress