13 messages in com.mysql.lists.mysqlRe: indexing problem | From | Sent On | Attachments |
|---|---|---|
| Arun Bhalla | 11 Sep 1999 14:43 | |
| Jani Tolonen | 13 Sep 1999 10:01 | |
| Arun Bhalla | 13 Sep 1999 10:23 | |
| Jani Tolonen | 13 Sep 1999 11:55 | |
| Arun Bhalla | 13 Sep 1999 13:34 | |
| Arun Bhalla | 13 Sep 1999 17:09 | |
| Michael Widenius | 15 Sep 1999 15:22 | |
| Isfarazi | 15 Sep 1999 16:54 | |
| Thimble Smith | 15 Sep 1999 16:57 | |
| Joshua Chamas | 15 Sep 1999 19:08 | |
| Steven T. Sigourney | 16 Sep 1999 13:08 | |
| Tonu Samuel | 16 Sep 1999 13:22 | |
| Jeff Urban | 16 Sep 1999 13:38 |
| Subject: | Re: indexing problem |
|---|---|
| From: | Arun Bhalla (abha...@simon.er.usgs.gov) |
| Date: | 09/13/1999 10:23:17 AM |
| List: | com.mysql.lists.mysql |
Jani Tolonen writes:
Arun Bhalla writes:
Hi, I'm using MySQL 3.23.2-alpha for sun-solaris2.6-sparc. My database consists of two tables, one being 350MB, the other being 4GB. I've indexed it pretty well; the indexes generally perform pretty well. However, for some reason, a small set of cases don't perform well.
Observe:
mysql> select sample_id from QW_SAMPLE where site_no = '293348094482800' ; ... 47 rows in set (29.97 sec)
mysql> select sample_id from QW_SAMPLE where site_no = '293348' ; Empty set (30.55 sec)
mysql> select sample_id from QW_SAMPLE where site_no = '293347' \g Empty set (0.00 sec)
mysql> select sample_id from QW_SAMPLE where site_no = '29334' ; Empty set (0.00 sec)
It appears that for certain cases, MySQL isn't using the key for site_no when it contains the initial substring '293348'.
This can be demonstrated with EXPLAIN:
mysql> explain select sample_id from QW_SAMPLE where site_no = '2933480944 82800' \G *************************** 1. row *************************** table: QW_SAMPLE type: ALL possible_keys: QW_SAMPLE_IE5_IX key: NULL key_len: NULL ref: NULL rows: 4009153 Extra: where used 1 row in set (0.00 sec)
mysql> explain select sample_id from QW_SAMPLE where site_no = '29334' \G *************************** 1. row *************************** table: QW_SAMPLE type: ref possible_keys: QW_SAMPLE_IE5_IX key: QW_SAMPLE_IE5_IX key_len: 15 ref: const rows: 1 Extra: where used 1 row in set (0.01 sec)
As seen, the key length is 15, so it should cover both cases. But it does n't.
Is there a good explanation for this, and hopefully a fix. On the advice of one, I did an "OPTIMIZE TABLE" which I didn't think would help, but I did it anyway. Obviously it didn't help.
Also, where can I get myisamchk, and would it help any?
Thanks, Arun
Hello Arun,
Just wondering, why do you use numbers quoted? If site_no is an integer type you can use just 29334 instead of '293334'.
site_no is defined as char(15).
Can you please include the output of the following:
shell> mysqldump database_name QW_SAMPLE
If you have a lot of data, you can include only so much that the problem can be reproduced, i.e. use option -w. See mysqldump --help
Thanks for your help. I've resolved the problem by inserting the afflicted data into a new table, deleting that data from QW_SAMPLE, and then reinserting the data into QW_SAMPLE. Why this works and OPTIMIZE TABLE does not -- maybe reindexing isn't performed on OPTIMIZE TABLE. Anyhow, I'm hoping the problem isn't hidden elsewhere in the database, so here's the info you requested.
# MySQL dump 5.12 # # Host: h2o.er.usgs.gov Database: qwdata #-------------------------------------------------------- # Server version 3.23.2-alpha
#
# Table structure for table 'QW_SAMPLE'
#
CREATE TABLE QW_SAMPLE (
sample_id int(11) NOT NULL auto_increment,
agency_cd char(5) DEFAULT '' NOT NULL,
site_no char(15) DEFAULT '' NOT NULL,
medium_cd char(1) DEFAULT '' NOT NULL,
sample_start_dt datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
sample_start_sg char(1) DEFAULT '' NOT NULL,
sample_end_dt datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
anl_stat_cd char(1) DEFAULT '' NOT NULL,
sample_end_sg char(1),
anl_src_cd char(1) DEFAULT '' NOT NULL,
hyd_cond_cd char(1) DEFAULT '' NOT NULL,
samp_type_cd char(1) DEFAULT '' NOT NULL,
hyd_event_cd char(1) DEFAULT '' NOT NULL,
project_cd char(9),
dist_proc_stat_cd char(1) DEFAULT '' NOT NULL,
aqfr_cd char(8),
lab_no char(7),
sample_cr datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
sample_accs_cd tinyint(4) DEFAULT '0' NOT NULL,
UNIQUE QW_SAMPLE_PK_IX (sample_id),
UNIQUE QW_SAMPLE_AK1_IX
(agency_cd,site_no,sample_start_dt,sample_start_sg,sample_end_dt,sample_end_sg,medium_cd),
KEY QW_SAMPLE_IE1_IX (project_cd),
KEY QW_SAMPLE_IE2_IX (dist_proc_stat_cd),
KEY QW_SAMPLE_IE3_IX (aqfr_cd),
KEY QW_SAMPLE_IE5_IX (site_no)
);
# # Dumping data for table 'QW_SAMPLE' # WHERE: site_no like '293348%' #
INSERT INTO QW_SAMPLE VALUES (2275325,'USGS','293348094482800','9','1982-04-27
09:53:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2275324,'USGS','293348094482800','9','1981-11-18
14:50:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2261872,'USGS','293348094482800','9','1979-11-06
14:07:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2261871,'USGS','293348094482800','9','1979-11-06
14:03:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2275323,'USGS','293348094482800','9','1981-11-18
14:48:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2254069,'USGS','293348094482800','9','1979-05-22
13:35:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2254068,'USGS','293348094482800','9','1979-05-22
13:30:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2268641,'USGS','293348094482800','9','1981-04-14
09:10:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2268640,'USGS','293348094482800','9','1981-04-14
09:08:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2254067,'USGS','293348094482800','9','1978-10-11
09:55:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2254066,'USGS','293348094482800','9','1978-10-11
09:54:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2268639,'USGS','293348094482800','9','1980-11-05
13:20:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2246821,'USGS','293348094482800','9','1978-06-07
09:49:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2246820,'USGS','293348094482800','9','1978-06-07
09:47:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2268638,'USGS','293348094482800','9','1980-11-05
13:18:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2246819,'USGS','293348094482800','9','1978-02-08
15:12:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2246818,'USGS','293348094482800','9','1978-02-08
15:10:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2261874,'USGS','293348094482800','9','1980-04-24
09:05:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2261873,'USGS','293348094482800','9','1980-04-24
09:02:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2275326,'USGS','293348094482800','9','1982-04-27
09:55:00','','0000-00-00
00:00:00','7',NULL,'9','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239185,'USGS','293348094482800','9','1977-05-05
10:20:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239184,'USGS','293348094482800','9','1977-04-26
11:01:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239183,'USGS','293348094482800','9','1977-04-26
10:59:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239182,'USGS','293348094482800','9','1977-04-26
10:57:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239181,'USGS','293348094482800','9','1977-04-26
10:55:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239180,'USGS','293348094482800','9','1977-02-03
14:02:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239179,'USGS','293348094482800','9','1977-02-03
14:00:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239178,'USGS','293348094482800','9','1976-12-13
10:32:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239177,'USGS','293348094482800','9','1976-12-13
10:30:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239176,'USGS','293348094482800','9','1976-11-18
10:32:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239175,'USGS','293348094482800','9','1976-11-18
10:30:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239174,'USGS','293348094482800','9','1976-10-21
12:57:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239188,'USGS','293348094482800','9','1977-05-11
09:12:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239187,'USGS','293348094482800','9','1977-05-11
09:10:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239173,'USGS','293348094482800','9','1976-10-21
12:55:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239186,'USGS','293348094482800','9','1977-05-05
10:22:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239199,'USGS','293348094482800','9','1977-08-26
12:22:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239198,'USGS','293348094482800','9','1977-08-26
12:20:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239197,'USGS','293348094482800','9','1977-06-16
11:17:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239196,'USGS','293348094482800','9','1977-06-16
11:15:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239195,'USGS','293348094482800','9','1977-05-31
16:14:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239194,'USGS','293348094482800','9','1977-05-31
16:12:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239193,'USGS','293348094482800','9','1977-05-31
16:10:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239192,'USGS','293348094482800','9','1977-05-19
09:32:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239191,'USGS','293348094482800','9','1977-05-19
09:30:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239190,'USGS','293348094482800','9','1977-05-17
10:32:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2239189,'USGS','293348094482800','9','1977-05-17
10:30:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2231561,'USGS','293348094520200','9','1976-07-24
12:01:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2231560,'USGS','293348094520200','9','1976-07-24
12:00:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2231559,'USGS','293348094520200','9','1976-07-19
10:32:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2231558,'USGS','293348094520200','9','1976-07-19
10:31:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2231557,'USGS','293348094520200','9','1976-07-19
10:30:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','',NULL,'1985-10-22 00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2231562,'USGS','293348095070601','6','1976-04-23
00:00:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','121EVGL',NULL,'1983-11-15
00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2231563,'USGS','293348095070602','6','1976-04-23
00:00:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','112CHCT',NULL,'1983-11-15
00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2231564,'USGS','293348095070603','6','1976-04-23
00:00:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','121EVGL',NULL,'1983-11-15
00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2231565,'USGS','293348095070604','6','1976-05-03
00:00:00','','0000-00-00
00:00:00','7',NULL,'A','A','9','9',NULL,'','121EVGL',NULL,'1983-11-15
00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2350136,'USGS','293348098334101','6','1998-11-06
14:09:00','','0000-00-00
00:00:00','H',NULL,'9','9','9','9',NULL,'','218EDRDA',NULL,'1998-09-30
00:00:00',0);
INSERT INTO QW_SAMPLE VALUES (2350127,'USGS','293348098334101','6','1998-11-06
14:00:00','','0000-00-00
00:00:00','H',NULL,'9','9','9','9',NULL,'','218edrda',NULL,'1998-09-30
00:00:00',0);
Arun