Shantanu's Blog

Labels: mysql case study, mysql tips

Labels: mysql, mysql case study

Labels: mysql case study, shell script

Labels: mysql case study, shell script

Finding rows those are NOT 10 digits long

I need to find the rows those are not 10 digits in length and must include a digit and not a alphabet.

CREATE TABLE mobile (mno varchar(100) default NULL);
INSERT INTO mobile VALUES ('223456780'),('2234567890'),('4234567890'),('6234567890'),('22345678aaaa0'),('123456789a');

mysql> select * from mobile;
+---------------+
| mno |
+---------------+
| 223456780 |
| 2234567890 |
| 4234567890 |
| 6234567890 |
| 22345678aaaa0 |
| 123456789a |
+---------------+
6 rows in set (0.00 sec)

mysql> select * from mobile WHERE length(mno) != 10;
+---------------+
| mno |
+---------------+
| 223456780 |
| 22345678aaaa0 |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from mobile WHERE mno NOT RLIKE '[0-9]{10}';
+---------------+
| mno |
+---------------+
| 223456780 |
| 22345678aaaa0 |
| 123456789a |
+---------------+
3 rows in set (0.00 sec)

Labels: mysql case study

ON DUPLICATE KEY insert into an audit or log table

Is there a way to accomplish this?

INSERT IGNORE INTO some_table (one,two,three) VALUES(1,2,3)
ON DUPLICATE KEY (INSERT INTO audit_table VALUES(NOW(),'Duplicate key ignored')

I really don't want to use PHP for this :(

http://stackoverflow.com/questions/3884344/mysql-on-duplicate-key-insert-into-an-audit-or-log-table
_____

If you want to consider using a stored procedure, you can use a DECLARE CONTINUE HANDLER. Here's an example:

CREATE TABLE users (
username VARCHAR(30),
first_name VARCHAR(30),
last_name VARCHAR(30),
PRIMARY KEY (username)
);

CREATE TABLE audit_table (timestamp datetime, description varchar(255));

DELIMITER $$
CREATE PROCEDURE add_user
(in_username VARCHAR(30),
in_first_name VARCHAR(30),
in_last_name VARCHAR(30))
MODIFIES SQL DATA
BEGIN
DECLARE duplicate_key INT DEFAULT 0;
BEGIN
DECLARE EXIT HANDLER FOR 1062 SET duplicate_key = 1;

INSERT INTO users (username, first_name, last_name)
VALUES (in_username, in_first_name, in_last_name);
END;

IF duplicate_key = 1 THEN
INSERT INTO audit_table VALUES(NOW(), 'Duplicate key ignored');
END IF;
END$$
DELIMITER ;

Let's add some data, trying to insert a duplicate key:

CALL add_user('userA', 'Bob', 'Smith');
CALL add_user('userB', 'Paul', 'Green');
CALL add_user('userA', 'Jack', 'Brown');

Result:

SELECT * FROM users;
+----------+------------+-----------+
| username | first_name | last_name |
+----------+------------+-----------+
| userA | Bob | Smith |
| userB | Paul | Green |
+----------+------------+-----------+
2 rows in set (0.00 sec)

SELECT * FROM audit_table;
+---------------------+-----------------------+
| timestamp | description |
+---------------------+-----------------------+
| 2010-10-07 20:17:35 | Duplicate key ignored |
+---------------------+-----------------------+
1 row in set (0.00 sec)

Labels: mysql case study

Finding records in a given date range

I'm very new to MySQL and I'm trying to query between 4 specific times each day;

1. Where >= PREVIOUS DAY 15:00:00 and < TODAY 11:00:00
OR
2. Where >= TODAY 11:00:00 and < TODAY 15:00:00

Is it even possible to use the NOW() plus a specific time? This would query each day. If anyone can helps, that would be great!

http://forums.mysql.com/read.php?22,388833,388833#msg-388833

Labels: mysql case study

Efficient full-text searchs on large sets of data

This is the answer to the question posted by one of the Experts on this blog.

http://community.livejournal.com/mysql/138390.html

If you want to search for a word in a given table, the best option is to use the 'Full Text Search' that is available only for the MyISAM tables.
As you can see from the following statements, I can search for the word "sham" without using Full Text indexes.
You will have to add the Stored procedure available here...

http://forge.mysql.com/tools/tool.php?id=204

The only statement that needs explanation is the call for the stored procedure. Here is how to use the procedure to explode almost any table to your advantage.

CALL mysql.normalize_table('test.Employee', 'id', 'Name', ' ');

"mysql" can be replaced with the DB Name where the procedure is stored.
"normalize_table" is the name of the procedure that you can download from URL mentioned above.
"test.Employee" is the table to be split.
"id" is the Unique key of that table (usually auto incremented primary key)
"Name" is the column name that needs to be exploded.
Space ' ' is the delimiter.

_____


mysql>drop table if exists mysql.SplitValues;
Query OK, 0 rows affected (0.00 sec)

mysql>CREATE TABLE mysql.SplitValues (cid varchar(40), value varchar(500) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql>drop table if exists test.Employee;
Query OK, 0 rows affected (0.00 sec)

mysql>CREATE TABLE test.Employee (
`id` int(11) default NULL,
`Name` varchar(50) NOT NULL,
`PhoneNo` varchar(15) default 'Unknown Phone',
PRIMARY KEY (`id`)
);
Query OK, 0 rows affected (0.03 sec)

mysql>INSERT INTO test.Employee VALUES (1, 'Manohar Ram','12345');
Query OK, 1 row affected (0.00 sec)

mysql>INSERT INTO test.Employee VALUES (2, 'Manohar1 sham','12345');
Query OK, 1 row affected (0.00 sec)

mysql>select * from test.Employee;
+----+---------------+---------+
| id | Name | PhoneNo |
+----+---------------+---------+
| 1 | Manohar Ram | 12345 |
| 2 | Manohar1 sham | 12345 |
+----+---------------+---------+
2 rows in set (0.00 sec)

mysql>CALL mysql.normalize_table('test.Employee', 'id', 'Name', ' ');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>select * from mysql.SplitValues;
+------+----------+---------------------+
| cid | value | dateadded |
+------+----------+---------------------+
| 1 | Manohar | 2010-10-27 08:18:56 |
| 1 | Ram | 2010-10-27 08:18:56 |
| 2 | Manohar1 | 2010-10-27 08:18:56 |
| 2 | sham | 2010-10-27 08:18:56 |
+------+----------+---------------------+
4 rows in set (0.00 sec)

mysql>alter table SplitValues add key (value, cid);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>select a.* from test.Employee as a inner join mysql.SplitValues as b on a.id = b.cid where b.value = 'sham';
+----+---------------+---------+
| id | Name | PhoneNo |
+----+---------------+---------+
| 2 | Manohar1 sham | 12345 |
+----+---------------+---------+
1 row in set (0.00 sec)

(08:22) mysql>explain select a.* from test.Employee as a inner join mysql.SplitValues as b on a.id = b.cid where b.value = 'sham';
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | b | ref | value | value | 503 | const | 1 | Using where; Using index |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | mysql.b.cid | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
2 rows in set (0.01 sec)

Labels: mysql case study, mysql tips

substring to return all values after delimiter
How do I get all the values after the first delimiter? In the following example I am expecting 'xyz@yahoo.com,pqr@company.com'

(02:40) mysql>select substring_index('abc@hotmail.com,xyz@yahoo.com,pqr@company.com', ',', 1) as first;
+-----------------+
| first |
+-----------------+
| abc@hotmail.com |
+-----------------+
1 row in set (0.00 sec)

(02:41) mysql>select substring_index('abc@hotmail.com,xyz@yahoo.com,pqr@company.com', ',', -1) as last;
+-----------------+
| last |
+-----------------+
| pqr@company.com |
+-----------------+
1 row in set (0.00 sec)


http://stackoverflow.com/questions/3606623/substring-to-return-all-values-after-delimiter

Labels: mysql case study

Grouping by Age Ranges (18-25,26-30,etc)

Hi all, I'm kind of stuck with a little problem. I know I can create PHP functions to create the results I want, but I was wondering if their is some unique way in mysql to accomplish this.

Basically here is my query to get a list of all the ages and a count of the number of employees for that age.

PHP Code:
SELECT FLOOR((TO_DAYS(NOW())-TO_DAYS(dob))/365.25) as Age,Count(db_id) as Count
FROM tbl_shareholder_list
WHERE current_employer = 'ASRC'
AND current_employee = 1
GROUP BY Age


What I'm trying to do is get a report that lumps a range of ages. So it comes out like

AgeRange --- Count
18-25 32
26-30 45
31-35 50

And so on and so on. Is there a trick to doing this or is it going to be a mess of IF statements and subqueries?

http://forums.devshed.com/mysql-help-4/grouping-by-age-ranges-18-25-26-30-etc-628617.html

Labels: mysql case study

Use String to SELECT from Database


I have a string:
$string = "12,15,22";

Each of these values represents a usrID and I'd like to use these to select the rows associated with the $string.

Something like this:

SELECT usrFirst, usrLast FROM tblusers WHERE usrID = 12 OR 15 OR 22.

Now, the $string changes all the time, and sometimes can be a different number of ID's. Eg., sometimes 3, sometimes 4.

http://stackoverflow.com/questions/1225658/php-mysql-use-string-to-select-from-database

Labels: mysql case study

Need REGEXP Matching in MySQL

I want to match a String which looks like this:

[lang_de]Hallo![/lang_de][lang_en]Hello![/lang_en]HeyHo[lang_es]Hola![/lang_es]

I want the matching to return true if there is text which is not enclosed by lang_tags (in this example: HeyHo). It could also be positioned at the beginning or end of the string.

Whitespace should NOT match, e.g. [lang_de]Tisch[/lang_de] [lang_en]Table[/lang_en] should not cause a match.

I can't use lookahead or lookback, because MySQL doesnt seem to support this.

Any Suggestions?

http://stackoverflow.com/questions/1224723/need-regexp-matching-in-mysql

Labels: mysql case study

How do I select the Openning_price of the first day of each week?

SELECT t1 . * , WEEK( t1.tradingday ) wk
FROM daily_prices t1
LEFT
JOIN daily_prices t2 ON week( t1.tradingday ) = WEEK( t2.tradingday )
AND t1.tradingday > t2.tradingday
WHERE t2.securityid IS NULL ;

Pretty Interesting!

http://forums.mysql.com/read.php?10,273597,273638#msg-273638

Labels: mysql case study

Finding Numbers

How do I find only INT, BIGINT numbers from a column?
For e.g. in the following column I want to return 43344433 only
And not 434-433 or any other row that has dash or comma or space.

mysql> select * from ztest where name REGEXP '[[:digit:]]-';
+-----------+
| name |
+-----------+
| 434-433 |
| 4334-4433 |
+-----------+
2 rows in set (0.00 sec)

mysql> select * from ztest ;
+--------------------------+
| name |
+--------------------------+
| '004500' '00984' |
| '0304500' , '020984' |
| '000304500' , '00020984' |
| '000304500' '00020984' |
| '000304500 00020984' |
| '000304500, 00020984' |
| 434-433 |
| 4334-4433 |
| 43344433 |
+--------------------------+
9 rows in set (0.00 sec)

Labels: mysql case study, mysql FAQ

Since I am using archive table in order to compress the data, I can't use any keys and still make the select query fast by taking advantage of the partitions.
You can create regular MyISAM or InnoDB tables with regular keys and use both, keys + partitions.
Sounds intereting, right?

drop table tr;

CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE) engine=archive
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);

INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');

SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

mysql> explain partitions SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tr
partitions: p2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where

mysql> SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-----------+------------+
| id | name | purchased |
+------+-----------+------------+
| 3 | TV set | 1996-03-10 |
| 10 | lava lamp | 1998-12-25 |
+------+-----------+------------+

If you have a key on purchased column and the table type is MyISAM or InnoDB then the explain plan will look like this...

mysql> explain partitions SELECT purchased FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tr
partitions: p2
type: range
possible_keys: purchased
key: purchased
key_len: 4
ref: NULL
rows: 1
Extra: Using where; Using index

The following query will let me know about the details of partitions from information schema...

mysql> SELECT TABLE_SCHEMA AS db, TABLE_NAME AS tb, PARTITION_NAME AS pName, TABLE_ROWS AS rows, PARTITION_EXPRESSION AS criterion, PARTITION_DESCRIPTION as ulimit, PARTITION_METHOD as method, CREATE_TIME as ctime, UPDATE_TIME as mtime FROM information_schema.partitions WHERE table_name = 'tr';
+-----------+----+-------+------+------------------+--------+--------+---------------------+---------------------+
| db | tb | pName | rows | criterion | ulimit | method | ctime | mtime |
+-----------+----+-------+------+------------------+--------+--------+---------------------+---------------------+
| shantanuo | tr | p0 | 3 | YEAR(purchased) | 1990 | RANGE | 2008-10-12 13:55:23 | 2008-10-12 13:55:24 |
| shantanuo | tr | p1 | 2 | YEAR(purchased) | 1995 | RANGE | 2008-10-12 13:55:23 | 2008-10-12 13:55:24 |
| shantanuo | tr | p2 | 2 | YEAR(purchased) | 2000 | RANGE | 2008-10-12 13:55:23 | 2008-10-12 13:55:24 |
| shantanuo | tr | p3 | 3 | YEAR(purchased) | 2005 | RANGE | 2008-10-12 13:55:23 | 2008-10-12 13:55:24 |
+-----------+----+-------+------+------------------+--------+--------+---------------------+---------------------+
4 rows in set (56.70 sec)

I tried it on a big test table and found that one should use partitions especially with archive table types. Since archive does not support indexes, some of the queries are very slow. But now I can take advantage of partitions and I do not need indexes because MySQL will fetch the data from a relatively small file. See the example given below.

The MyISAM table with basic indexes consumed 20 GB to store 72,88,953 rows.

-rw-r----- 1 mysql mysql 11K Oct 14 17:17 Feedback.frm
-rw-r----- 1 mysql mysql 20G Oct 14 17:48 Feedback.MYD
-rw-r----- 1 mysql mysql 567M Oct 14 17:49 Feedback.MYI

The archive table is less than 2 GB (<90%) and partitioning broke it up to 9 files of about 300 MB each.

-rw-rw---- 1 mysql mysql 11K Oct 15 17:46 Feedback_archive2.frm
-rw-rw---- 1 mysql mysql 76 Oct 15 17:46 Feedback_archive2.par
-rw-rw---- 1 mysql mysql 88 Oct 15 17:46 Feedback_archive2#P#p803.ARZ
-rw-rw---- 1 mysql mysql 23M Oct 16 03:30 Feedback_archive2#P#p804.ARZ
-rw-rw---- 1 mysql mysql 108M Oct 16 03:30 Feedback_archive2#P#p805.ARZ
-rw-rw---- 1 mysql mysql 352M Oct 16 03:30 Feedback_archive2#P#p806.ARZ
-rw-rw---- 1 mysql mysql 308M Oct 16 03:30 Feedback_archive2#P#p807.ARZ
-rw-rw---- 1 mysql mysql 305M Oct 16 03:30 Feedback_archive2#P#p808.ARZ
-rw-rw---- 1 mysql mysql 375M Oct 16 03:30 Feedback_archive2#P#p809.ARZ
-rw-rw---- 1 mysql mysql 366M Oct 16 03:30 Feedback_archive2#P#p810.ARZ
-rw-rw---- 1 mysql mysql 134M Oct 16 03:30 Feedback_archive2#P#p811.ARZ

If I remove some of the unnecessary columns from the table then the size of the table is again reduced by 1GB as shown below.

-rw-rw---- 1 mysql mysql 15K Oct 14 18:41 Feedback_archive.frm
-rw-rw---- 1 mysql mysql 76 Oct 14 18:41 Feedback_archive.par
-rw-rw---- 1 mysql mysql 88 Oct 14 18:41 Feedback_archive#P#p803.ARZ
-rw-rw---- 1 mysql mysql 11M Oct 15 03:30 Feedback_archive#P#p804.ARZ
-rw-rw---- 1 mysql mysql 47M Oct 15 03:30 Feedback_archive#P#p805.ARZ
-rw-rw---- 1 mysql mysql 155M Oct 15 03:30 Feedback_archive#P#p806.ARZ
-rw-rw---- 1 mysql mysql 146M Oct 15 03:30 Feedback_archive#P#p807.ARZ
-rw-rw---- 1 mysql mysql 149M Oct 15 03:30 Feedback_archive#P#p808.ARZ
-rw-rw---- 1 mysql mysql 169M Oct 15 03:30 Feedback_archive#P#p809.ARZ
-rw-rw---- 1 mysql mysql 154M Oct 15 03:30 Feedback_archive#P#p810.ARZ
-rw-rw---- 1 mysql mysql 58M Oct 15 03:30 Feedback_archive#P#p811.ARZ

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'Feedback_archive';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p803 | 0 |
| p804 | 85679 |
| p805 | 390157 |
| p806 | 1319141 |
| p807 | 1229578 |
| p808 | 1276819 |
| p809 | 1287831 |
| p810 | 1164843 |
| p811 | 534905 |
+----------------+------------+
9 rows in set (0.00 sec)

And the relevant extract of the create table statement looks like this...

) ENGINE=ARCHIVE PARTITION BY RANGE (extract(year_month from (FeedDate)))
(PARTITION p803 VALUES LESS THAN (200803),
PARTITION p804 VALUES LESS THAN (200804),
PARTITION p805 VALUES LESS THAN (200805),
PARTITION p806 VALUES LESS THAN (200806),
PARTITION p807 VALUES LESS THAN (200807),
PARTITION p808 VALUES LESS THAN (200808),
PARTITION p809 VALUES LESS THAN (200809),
PARTITION p810 VALUES LESS THAN (200810),
PARTITION p811 VALUES LESS THAN (200811))

Labels: mysql 5.1, mysql case study

Hi, I need helping sorting and getting some files.

I have these values
[id] | [id2] [date]
1 | 25 | 2007-03-01
2 | 25 | 2008-03-02
3 | 25 | 2008-03-06
4 | 25 | 2008-03-05
5 | 165 | 2008-04-03
9 | 168 | 2008-05-06
10 | 165 | 2008-05-06
11 | 165 | 2008-05-08
12 | 165 | 2008-05-02

and need to get the highest date of each id2
this would have to give me
3|25|2008-03-06
11 | 165 | 2008-05-08
9 | 168 | 2008-05-06

but is giving me:
1 | 25 | 2008-03-06
5 | 165 | 2008-05-08
9 | 168 | 2008-05-06

using SELECT id, id2, max( act_date ) AS act_date
FROM upg
GROUP BY id2

i dont know why id is giving me differents ids

please help anyone

http://forums.mysql.com/read.php?10,208591,208591#msg-208591

Labels: mysql case study

case when
I have a table that is part of a membership database for a professional organization. There are several columns for the various designations and certifications that members can have with a 1 or 0 depending on the member's designations. Here is an abbreviated example:
Code:

lastname | firstname | CFM | PE | RLA
=========|===========|=====|====|======
Smith | John | 1 | 0 | 1

I am trying to build a query that will return "John Smith CFM RLA"

Is this possible? I know I can do it by scripting it out in PHP but I'm hoping there is a way to do it without scripting. I appreciate any thoughts or guidance.

http://www.sitepoint.com/forums/showthread.php?p=3789541#post3789541

Labels: mysql case study

Trying to keep this simple,
I have many pickers and I am looking to find:
How many apples, how many oranges picked today and total of all fruit so far for each picker..

[picker] [apples] [oranges [date]
a 0 50 20080102
b 0 75 20080102
c 0 30 20080102
a 20 0 20080103
b 50 0 20080103
c 10 0 20080103
a 0 95 20080104
b 100 0 20080104
c 0 85 20080104

The results I am trying to get are something like what follows:
[picker] [apples] [oranges] [date] [total fruit]
a 0 95 20080104 165
b 100 0 20080104 225
c 0 85 20080104 125

I can can easily get the most current rows for each picker
select picker, apples, oranges from work where date='20080104',
I can easily get the total fruit for each picker
select picker, (sum(apples) + sum(oranges)) from work group by picker,
I just cannot seem to cram it all in one sql query.

I have looked at 'WITH ROLLUP' but it does not seem to do what I need to do.

http://forums.mysql.com/read.php?10,207297,207297#msg-207297

Labels: mysql case study

Order By problem

Here is a sample set of data (ignore the dashes - - - - - - between columns):

topic_id - - - message_time

1 - - - - - - 2008-04-12 16:27:56
1 - - - - - - 2008-04-12 16:27:56
1 - - - - - - 2008-04-12 16:27:56
2 - - - - - - 2008-04-13 16:27:56
1 - - - - - - 2008-04-26 17:27:56
2 - - - - - - 2008-04-26 18:27:56
2 - - - - - - 2008-04-26 19:27:56
2 - - - - - - 2008-04-26 20:27:56

What I would like have happen is for the topics that have the most entries within the last seven days to be returned first. If there is a tie, it should go to the one with the most recent entry.

Below is the query I started with, but it doesn't seem to even be getting the first part of what I want. Any help would be greatly appreciated.

SELECT DISTINCT(topic_id) AS topic_id
FROM messages
ORDER BY COUNT(message_time) >= NOW()
LIMIT 0,25;

http://forums.mysql.com/read.php?10,207167,207167#msg-207167

Labels: mysql case study

select rows with max value?
Given a table like this:

CREATE TABLE IF NOT EXISTS `people` (
`name` varchar(10) NOT NULL,
`age` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `people` (`name`, `age`) VALUES ('bob', 13), ('john', 25), ('mike', 25), ('steve', 8), ('sue', 13);

How would I select only the rows with the highest 'age' value?
In other words, because "john" and "mike" are the oldest, I want only their rows.

http://forums.mysql.com/read.php?10,199598,199598#msg-199598

Labels: mysql case study