dbTalk Databases Forums  

MySQL 5.5 partitions problem

Go Back dbTalk Databases Forums Databases comp.databases.mysql MySQL 5.5 partitions problem

comp.databases.mysql comp.databases.mysql


Discuss MySQL 5.5 partitions problem in the comp.databases.mysql forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
David
 
Posts: n/a

Default MySQL 5.5 partitions problem - 04-18-2011 , 03:58 AM





Hi guys, I just upgraded our database to 5.5 so I could start using
partitions. I created a test table to make sure partitions were
working and sure enough they was. So I then created the main table as
follows:

mysql>SHOW CREATE TABLE `logger`\G

*************************** 1. row ***************************
Table: logger
Create Table: CREATE TABLE `logger` (
`id` int(15) unsigned NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`account_no` char(11) NOT NULL,
`database_area` char(100) NOT NULL,
`col_name` varchar(255) DEFAULT NULL,
`before` varchar(255) DEFAULT NULL,
`after` varchar(255) DEFAULT NULL,
`uid` int(11) NOT NULL,
PRIMARY KEY (`id`,`date`),
KEY `account_no` (`account_no`),
KEY `col_name` (`col_name`),
KEY `database_area` (`database_area`),
KEY `after` (`after`),
KEY `before` (`before`)
) ENGINE=MyISAM AUTO_INCREMENT=514714 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(`date`)
(PARTITION p0 VALUES LESS THAN ('2009-01-01 00:00:00') ENGINE =
MyISAM,
PARTITION p1 VALUES LESS THAN ('2009-04-01 00:00:00') ENGINE =
MyISAM,
PARTITION p2 VALUES LESS THAN ('2009-07-01 00:00:00') ENGINE =
MyISAM,
PARTITION p3 VALUES LESS THAN ('2009-10-01 00:00:00') ENGINE =
MyISAM,
PARTITION p4 VALUES LESS THAN ('2010-01-01 00:00:00') ENGINE =
MyISAM,
PARTITION p5 VALUES LESS THAN ('2010-04-01 00:00:00') ENGINE =
MyISAM,
PARTITION p6 VALUES LESS THAN ('2010-07-01 00:00:00') ENGINE =
MyISAM,
PARTITION p7 VALUES LESS THAN ('2010-10-01 00:00:00') ENGINE =
MyISAM,
PARTITION p8 VALUES LESS THAN ('2011-01-01 00:00:00') ENGINE =
MyISAM,
PARTITION p9 VALUES LESS THAN ('2011-04-01 00:00:00') ENGINE =
MyISAM,
PARTITION p10 VALUES LESS THAN ('2011-07-01 00:00:00') ENGINE =
MyISAM,
PARTITION p11 VALUES LESS THAN ('2011-10-01 00:00:00') ENGINE =
MyISAM,
PARTITION p12 VALUES LESS THAN ('2012-01-01 00:00:00') ENGINE =
MyISAM,
PARTITION p13 VALUES LESS THAN ('2012-04-01 00:00:00') ENGINE =
MyISAM,
PARTITION p14 VALUES LESS THAN ('2012-07-01 00:00:00') ENGINE =
MyISAM,
PARTITION p15 VALUES LESS THAN ('2012-10-01 00:00:00') ENGINE =
MyISAM,
PARTITION p16 VALUES LESS THAN ('2013-01-01 00:00:00') ENGINE =
MyISAM,
PARTITION p17 VALUES LESS THAN ('2013-04-01 00:00:00') ENGINE =
MyISAM,
PARTITION p18 VALUES LESS THAN ('2013-07-01 00:00:00') ENGINE =
MyISAM,
PARTITION p19 VALUES LESS THAN ('2013-10-01 00:00:00') ENGINE =
MyISAM,
PARTITION p20 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
1 row in set (0.01 sec)

I imported all our existing data into the table and tested it:


mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'logger';
+----------------+------------+
Quote:
PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
p0 | 18 |
p1 | 4671 |
p2 | 118171 |
p3 | 30875 |
p4 | 28924 |
p5 | 10334 |
p6 | 25045 |
p7 | 12661 |
p8 | 28544 |
p9 | 185148 |
p10 | 70322 |
p11 | 0 |
p12 | 0 |
p13 | 0 |
p14 | 0 |
p15 | 0 |
p16 | 0 |
p17 | 0 |
p18 | 0 |
p19 | 0 |
p20 | 0 |
+----------------+------------+
21 rows in set (0.00 sec)

However when i run queries against it, it doesnt seem to select the
partitions correctly:


mysql> EXPLAIN PARTITIONS SELECT `id` FROM `logger` WHERE `date`
BETWEEN '2009-07-01 00:00:00' and '2009-10-01 00:00:00'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: logger
partitions:
p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14, p15,p16,p17,p18,p19,p20
type: index
possible_keys: NULL
key: PRIMARY
key_len: 12
ref: NULL
rows: 514713
Extra: Using where; Using index
1 row in set (0.00 sec)

As you can see it is showing all partitions instead of p2 and p3.

I would be grateful if anyone could shed some light into what I am
doing wrong.

Regards
Dave.

Reply With Quote
  #2  
Old   
onedbguru
 
Posts: n/a

Default Re: MySQL 5.5 partitions problem - 04-18-2011 , 08:04 PM





On Apr 18, 4:58*am, David <david.greenh... (AT) gmail (DOT) com> wrote:
Quote:
Hi guys, I just upgraded our database to 5.5 so I could start using
partitions. I created a test table to make sure partitions were
working and sure enough they was. So I then created the main table as
follows:

mysql>SHOW CREATE TABLE `logger`\G

*************************** 1. row ***************************
* * * *Table: logger
Create Table: CREATE TABLE `logger` (
* `id` int(15) unsigned NOT NULL AUTO_INCREMENT,
* `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
* `account_no` char(11) NOT NULL,
* `database_area` char(100) NOT NULL,
* `col_name` varchar(255) DEFAULT NULL,
* `before` varchar(255) DEFAULT NULL,
* `after` varchar(255) DEFAULT NULL,
* `uid` int(11) NOT NULL,
* PRIMARY KEY (`id`,`date`),
* KEY `account_no` (`account_no`),
* KEY `col_name` (`col_name`),
* KEY `database_area` (`database_area`),
* KEY `after` (`after`),
* KEY `before` (`before`)
) ENGINE=MyISAM AUTO_INCREMENT=514714 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE *COLUMNS(`date`)
(PARTITION p0 VALUES LESS THAN ('2009-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p1 VALUES LESS THAN ('2009-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p2 VALUES LESS THAN ('2009-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p3 VALUES LESS THAN ('2009-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p4 VALUES LESS THAN ('2010-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p5 VALUES LESS THAN ('2010-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p6 VALUES LESS THAN ('2010-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p7 VALUES LESS THAN ('2010-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p8 VALUES LESS THAN ('2011-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p9 VALUES LESS THAN ('2011-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p10 VALUES LESS THAN ('2011-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p11 VALUES LESS THAN ('2011-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p12 VALUES LESS THAN ('2012-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p13 VALUES LESS THAN ('2012-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p14 VALUES LESS THAN ('2012-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p15 VALUES LESS THAN ('2012-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p16 VALUES LESS THAN ('2013-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p17 VALUES LESS THAN ('2013-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p18 VALUES LESS THAN ('2013-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p19 VALUES LESS THAN ('2013-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p20 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
1 row in set (0.01 sec)

I imported all our existing data into the table and tested it:

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'logger';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 * * * * * * | * * * * 18 |
| p1 * * * * * * | * * * 4671 |
| p2 * * * * * * | * * 118171 |
| p3 * * * * * * | * * *30875 |
| p4 * * * * * * | * * *28924 |
| p5 * * * * * * | * * *10334 |
| p6 * * * * * * | * * *25045 |
| p7 * * * * * * | * * *12661 |
| p8 * * * * * * | * * *28544 |
| p9 * * * * * * | * * 185148 |
| p10 * * * * * *| * * *70322 |
| p11 * * * * * *| * * * * *0 |
| p12 * * * * * *| * * * * *0 |
| p13 * * * * * *| * * * * *0 |
| p14 * * * * * *| * * * * *0 |
| p15 * * * * * *| * * * * *0 |
| p16 * * * * * *| * * * * *0 |
| p17 * * * * * *| * * * * *0 |
| p18 * * * * * *| * * * * *0 |
| p19 * * * * * *| * * * * *0 |
| p20 * * * * * *| * * * * *0 |
+----------------+------------+
21 rows in set (0.00 sec)

However when i run queries against it, it doesnt seem to select the
partitions correctly:

mysql> EXPLAIN PARTITIONS SELECT `id` FROM `logger` WHERE `date`
BETWEEN '2009-07-01 00:00:00' and '2009-10-01 00:00:00'\G
*************************** 1. row ***************************
* * * * * *id: 1
* select_type: SIMPLE
* * * * table: logger
* *partitions:
p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14, p15,p16,p17,p18,p19,p20
* * * * *type: index
possible_keys: NULL
* * * * * key: PRIMARY
* * * key_len: 12
* * * * * ref: NULL
* * * * *rows: 514713
* * * * Extra: Using where; Using index
1 row in set (0.00 sec)

As you can see it is showing all partitions instead of p2 and p3.

I would be grateful if anyone could shed some light into what I am
doing wrong.

Regards
Dave.

It appears to have used just the PK index (id,date). Did you analyze
the table before executing your query?

Reply With Quote
  #3  
Old   
Tony Mountifield
 
Posts: n/a

Default Re: MySQL 5.5 partitions problem - 04-19-2011 , 06:34 AM


In article <d5adc5cb-77a1-4621-9b89-723beb7b2347 (AT) hd10g2000vbb (DOT) googlegroups.com>,
onedbguru <onedbguru (AT) yahoo (DOT) com> wrote:
Quote:
On Apr 18, 4:58*am, David <david.greenh... (AT) gmail (DOT) com> wrote:
Hi guys, I just upgraded our database to 5.5 so I could start using
partitions. I created a test table to make sure partitions were
working and sure enough they was. So I then created the main table as
follows:

mysql>SHOW CREATE TABLE `logger`\G

*************************** 1. row ***************************
* * * *Table: logger
Create Table: CREATE TABLE `logger` (
* `id` int(15) unsigned NOT NULL AUTO_INCREMENT,
* `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
* `account_no` char(11) NOT NULL,
* `database_area` char(100) NOT NULL,
* `col_name` varchar(255) DEFAULT NULL,
* `before` varchar(255) DEFAULT NULL,
* `after` varchar(255) DEFAULT NULL,
* `uid` int(11) NOT NULL,
* PRIMARY KEY (`id`,`date`),
* KEY `account_no` (`account_no`),
* KEY `col_name` (`col_name`),
* KEY `database_area` (`database_area`),
* KEY `after` (`after`),
* KEY `before` (`before`)
) ENGINE=MyISAM AUTO_INCREMENT=514714 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE *COLUMNS(`date`)
(PARTITION p0 VALUES LESS THAN ('2009-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p1 VALUES LESS THAN ('2009-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p2 VALUES LESS THAN ('2009-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p3 VALUES LESS THAN ('2009-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p4 VALUES LESS THAN ('2010-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p5 VALUES LESS THAN ('2010-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p6 VALUES LESS THAN ('2010-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p7 VALUES LESS THAN ('2010-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p8 VALUES LESS THAN ('2011-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p9 VALUES LESS THAN ('2011-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p10 VALUES LESS THAN ('2011-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p11 VALUES LESS THAN ('2011-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p12 VALUES LESS THAN ('2012-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p13 VALUES LESS THAN ('2012-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p14 VALUES LESS THAN ('2012-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p15 VALUES LESS THAN ('2012-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p16 VALUES LESS THAN ('2013-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p17 VALUES LESS THAN ('2013-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p18 VALUES LESS THAN ('2013-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p19 VALUES LESS THAN ('2013-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p20 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
1 row in set (0.01 sec)

I imported all our existing data into the table and tested it:

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'logger';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 * * * * * * | * * * * 18 |
| p1 * * * * * * | * * * 4671 |
| p2 * * * * * * | * * 118171 |
| p3 * * * * * * | * * *30875 |
| p4 * * * * * * | * * *28924 |
| p5 * * * * * * | * * *10334 |
| p6 * * * * * * | * * *25045 |
| p7 * * * * * * | * * *12661 |
| p8 * * * * * * | * * *28544 |
| p9 * * * * * * | * * 185148 |
| p10 * * * * * *| * * *70322 |
| p11 * * * * * *| * * * * *0 |
| p12 * * * * * *| * * * * *0 |
| p13 * * * * * *| * * * * *0 |
| p14 * * * * * *| * * * * *0 |
| p15 * * * * * *| * * * * *0 |
| p16 * * * * * *| * * * * *0 |
| p17 * * * * * *| * * * * *0 |
| p18 * * * * * *| * * * * *0 |
| p19 * * * * * *| * * * * *0 |
| p20 * * * * * *| * * * * *0 |
+----------------+------------+
21 rows in set (0.00 sec)

However when i run queries against it, it doesnt seem to select the
partitions correctly:

mysql> EXPLAIN PARTITIONS SELECT `id` FROM `logger` WHERE `date`
BETWEEN '2009-07-01 00:00:00' and '2009-10-01 00:00:00'\G
*************************** 1. row ***************************
* * * * * *id: 1
* select_type: SIMPLE
* * * * table: logger
* *partitions:
p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14, p15,p16,p17,p18,p19,p20
* * * * *type: index
possible_keys: NULL
* * * * * key: PRIMARY
* * * key_len: 12
* * * * * ref: NULL
* * * * *rows: 514713
* * * * Extra: Using where; Using index
1 row in set (0.00 sec)

As you can see it is showing all partitions instead of p2 and p3.

I would be grateful if anyone could shed some light into what I am
doing wrong.
Try adding: KEY `date` (`date`),

At the moment, it doesn't have any index beginning with `date`.
That is why the explain says "possible_keys: NULL" and can't choose
the specific partitions.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

Reply With Quote
  #4  
Old   
David
 
Posts: n/a

Default Re: MySQL 5.5 partitions problem - 04-20-2011 , 07:04 AM


On Apr 19, 12:34*pm, t... (AT) mountifield (DOT) org (Tony Mountifield) wrote:
Quote:
In article <d5adc5cb-77a1-4621-9b89-723beb7b2... (AT) hd10g2000vbb (DOT) googlegroups.com>,









onedbguru *<onedbg... (AT) yahoo (DOT) com> wrote:
On Apr 18, 4:58*am, David <david.greenh... (AT) gmail (DOT) com> wrote:
Hi guys, I just upgraded our database to 5.5 so I could start using
partitions. I created a test table to make sure partitions were
working and sure enough they was. So I then created the main table as
follows:

mysql>SHOW CREATE TABLE `logger`\G

*************************** 1. row ***************************
* * * *Table: logger
Create Table: CREATE TABLE `logger` (
* `id` int(15) unsigned NOT NULL AUTO_INCREMENT,
* `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
* `account_no` char(11) NOT NULL,
* `database_area` char(100) NOT NULL,
* `col_name` varchar(255) DEFAULT NULL,
* `before` varchar(255) DEFAULT NULL,
* `after` varchar(255) DEFAULT NULL,
* `uid` int(11) NOT NULL,
* PRIMARY KEY (`id`,`date`),
* KEY `account_no` (`account_no`),
* KEY `col_name` (`col_name`),
* KEY `database_area` (`database_area`),
* KEY `after` (`after`),
* KEY `before` (`before`)
) ENGINE=MyISAM AUTO_INCREMENT=514714 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE *COLUMNS(`date`)
(PARTITION p0 VALUES LESS THAN ('2009-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p1 VALUES LESS THAN ('2009-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p2 VALUES LESS THAN ('2009-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p3 VALUES LESS THAN ('2009-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p4 VALUES LESS THAN ('2010-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p5 VALUES LESS THAN ('2010-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p6 VALUES LESS THAN ('2010-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p7 VALUES LESS THAN ('2010-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p8 VALUES LESS THAN ('2011-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p9 VALUES LESS THAN ('2011-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p10 VALUES LESS THAN ('2011-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p11 VALUES LESS THAN ('2011-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p12 VALUES LESS THAN ('2012-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p13 VALUES LESS THAN ('2012-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p14 VALUES LESS THAN ('2012-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p15 VALUES LESS THAN ('2012-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p16 VALUES LESS THAN ('2013-01-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p17 VALUES LESS THAN ('2013-04-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p18 VALUES LESS THAN ('2013-07-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p19 VALUES LESS THAN ('2013-10-01 00:00:00') ENGINE =
MyISAM,
*PARTITION p20 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */
1 row in set (0.01 sec)

I imported all our existing data into the table and tested it:

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'logger';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 * * * * * * | * * * * 18 |
| p1 * * * * * * | * * * 4671 |
| p2 * * * * * * | * * 118171 |
| p3 * * * * * * | * * *30875 |
| p4 * * * * * * | * * *28924 |
| p5 * * * * * * | * * *10334 |
| p6 * * * * * * | * * *25045 |
| p7 * * * * * * | * * *12661 |
| p8 * * * * * * | * * *28544 |
| p9 * * * * * * | * * 185148 |
| p10 * * * * * *| * * *70322 |
| p11 * * * * * *| * * * * *0 |
| p12 * * * * * *| * * * * *0 |
| p13 * * * * * *| * * * * *0 |
| p14 * * * * * *| * * * * *0 |
| p15 * * * * * *| * * * * *0 |
| p16 * * * * * *| * * * * *0 |
| p17 * * * * * *| * * * * *0 |
| p18 * * * * * *| * * * * *0 |
| p19 * * * * * *| * * * * *0 |
| p20 * * * * * *| * * * * *0 |
+----------------+------------+
21 rows in set (0.00 sec)

However when i run queries against it, it doesnt seem to select the
partitions correctly:

mysql> EXPLAIN PARTITIONS SELECT `id` FROM `logger` WHERE `date`
BETWEEN '2009-07-01 00:00:00' and '2009-10-01 00:00:00'\G
*************************** 1. row ***************************
* * * * * *id: 1
* select_type: SIMPLE
* * * * table: logger
* *partitions:
p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14, p15,p16,p17,p18,p19,p20
* * * * *type: index
possible_keys: NULL
* * * * * key: PRIMARY
* * * key_len: 12
* * * * * ref: NULL
* * * * *rows: 514713
* * * * Extra: Using where; Using index
1 row in set (0.00 sec)

As you can see it is showing all partitions instead of p2 and p3.

I would be grateful if anyone could shed some light into what I am
doing wrong.

Try adding: KEY `date` (`date`),

At the moment, it doesn't have any index beginning with `date`.
That is why the explain says "possible_keys: NULL" and can't choose
the specific partitions.

Cheers
Tony
--
Tony Mountifield
Work: t... (AT) softins (DOT) co.uk -http://www.softins.co.uk
Play: t... (AT) mountifield (DOT) org -http://tony.mountifield.org
Very strange, I removed the my.cnf file and restarted mysql and it
worked fine. Put the conf file back - restarted and its back to how it
was above. So something in the my.cnf is doing this!! will have to go
through every line to find out the culprit.

Dave.

Reply With Quote
  #5  
Old   
Tony Mountifield
 
Posts: n/a

Default Re: MySQL 5.5 partitions problem - 04-20-2011 , 08:14 AM


In article <675041d1-51ac-46b3-812a-f6739a765e88 (AT) e26g2000vbz (DOT) googlegroups.com>,
David <david.greenhall (AT) gmail (DOT) com> wrote:
Quote:
On Apr 19, 12:34*pm, t... (AT) mountifield (DOT) org (Tony Mountifield) wrote:
I would be grateful if anyone could shed some light into what I am
doing wrong.

Try adding: KEY `date` (`date`),

At the moment, it doesn't have any index beginning with `date`.
That is why the explain says "possible_keys: NULL" and can't choose
the specific partitions.

Very strange, I removed the my.cnf file and restarted mysql and it
worked fine. Put the conf file back - restarted and its back to how it
was above. So something in the my.cnf is doing this!! will have to go
through every line to find out the culprit.
Even so, you still need an index on `date` in order to make your query
efficient. Having it in the second column of the `id` index is not enough.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top