Index on MERGE table
Hi everyone! I have a bunch of MyISAM tables and one MERGE table. All have same structure. It seems to me indecies on MERGE table aren't fine. When I fetch rows by indexed field I get empty result set. Index size showed by 'SHOW TABLE STATUS' is 0. SHOW INDEXES also shows somthing strange. Index cardinality is 81 while table contains over than 12 millions of different values. I've tried recreating indecies but this didn't help. I couldn't find anything relevant in documentation. How should I create index on MERGE table? Thanks in advance! -- Regards, Eugene Kosov. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index on MERGE table
Hi Eugene, I suppose you have read: http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html Especially the paragraph starting with: The order of indexes in the MERGE table and its underlying tables should be the same. HTH, Cor - Original Message - From: Eugene Kosov [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 2:58 PM Subject: Index on MERGE table Hi everyone! I have a bunch of MyISAM tables and one MERGE table. All have same structure. It seems to me indecies on MERGE table aren't fine. When I fetch rows by indexed field I get empty result set. Index size showed by 'SHOW TABLE STATUS' is 0. SHOW INDEXES also shows somthing strange. Index cardinality is 81 while table contains over than 12 millions of different values. I've tried recreating indecies but this didn't help. I couldn't find anything relevant in documentation. How should I create index on MERGE table? Thanks in advance! -- Regards, Eugene Kosov. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index on MERGE table
Oops! I think I've missed it... Thanks a lot! :) C.R.Vegelin пишет: Hi Eugene, I suppose you have read: http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html Especially the paragraph starting with: The order of indexes in the MERGE table and its underlying tables should be the same. HTH, Cor - Original Message - From: Eugene Kosov [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 2:58 PM Subject: Index on MERGE table Hi everyone! I have a bunch of MyISAM tables and one MERGE table. All have same structure. It seems to me indecies on MERGE table aren't fine. When I fetch rows by indexed field I get empty result set. Index size showed by 'SHOW TABLE STATUS' is 0. SHOW INDEXES also shows somthing strange. Index cardinality is 81 while table contains over than 12 millions of different values. I've tried recreating indecies but this didn't help. I couldn't find anything relevant in documentation. How should I create index on MERGE table? Thanks in advance! -- Regards, Eugene Kosov. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT slow after adding INDEX on MERGE table
-Original Message- From: Sergei Golubchik [mailto:serg;mysql.com] Sent: 10 November 2002 12:23 To: Christian Sylvestre Cc: [EMAIL PROTECTED] Subject: Re: SELECT slow after adding INDEX on MERGE table Hi! What is the query, and what does EXPLAIN say ? The query is: SELECT DISTINCT(biuserid) FROM accesslog And the result from EXPLAIN is: table typepossible_keys key key_len ref rowsExtra etg_bi_accesslogindex NULLi1 11 NULL442157 Using index Cheers, Christian On Nov 08, Christian Sylvestre wrote: I have 3 identical tables with approx 150,000 row each. These tables have currently no indexes. If I do a specific query on each table it takes approx 3 seconds per table. Doing the same query on the MERGE tables takes approx 8.5 seconds. I then created an index on the tables (identical index on each one...). Doing the same query on the individual tables takes about 1 second. If I then query the MERGE table (before defining the new index on the MERGE table) it takes exactly the same time to get the results (approx 8.5 seconds). Then I defined the INDEX on the MERGE table. I would expect the query to take approx 3 seconds, but not at all, the query now takes more then a 100 seconds to execute I really dont understand what is happening. Maybe there is something I am not getting right regarding indexes and MERGE tables! I added the tables definition (with the index) below. If anyone has any idea on what is wrong. Cheers, Christian --- I am using MySQL 3.23.49 The query that I am doing is: SELECT DISTINCT(biuserid) FROM accesslog Table definition: CREATE TABLE accesslog ( LOGID int(11) NOT NULL default '0', PAGEID varchar(10) NOT NULL default '', BIUSERID varchar(10) default NULL, DATEACCESSED date NOT NULL default '-00-00', HOST varchar(128) NOT NULL default '', OWNERID char(3) NOT NULL default '', SESSIONID varchar(20) default NULL, TIMEACCESSED time NOT NULL default '00:00:00', KEY i1 (BIUSERID) ) TYPE=MRG_MyISAM UNION=(accesslog_2002_11_04,accesslog_2002_11_05,accesslog_2002_11_06) ; CREATE TABLE accesslog_2002_11_04 ( logid int(11) NOT NULL default '0', PAGEID varchar(10) NOT NULL default '', BIUSERID varchar(10) default NULL, DATEACCESSED date NOT NULL default '-00-00', HOST varchar(128) NOT NULL default '', OWNERID char(3) NOT NULL default '', SESSIONID varchar(20) default NULL, TIMEACCESSED time NOT NULL default '00:00:00', KEY i1 (BIUSERID) ) TYPE=MyISAM; Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT slow after adding INDEX on MERGE table
Hi! What is the query, and what does EXPLAIN say ? On Nov 08, Christian Sylvestre wrote: I have 3 identical tables with approx 150,000 row each. These tables have currently no indexes. If I do a specific query on each table it takes approx 3 seconds per table. Doing the same query on the MERGE tables takes approx 8.5 seconds. I then created an index on the tables (identical index on each one...). Doing the same query on the individual tables takes about 1 second. If I then query the MERGE table (before defining the new index on the MERGE table) it takes exactly the same time to get the results (approx 8.5 seconds). Then I defined the INDEX on the MERGE table. I would expect the query to take approx 3 seconds, but not at all, the query now takes more then a 100 seconds to execute I really dont understand what is happening. Maybe there is something I am not getting right regarding indexes and MERGE tables! I added the tables definition (with the index) below. If anyone has any idea on what is wrong. Cheers, Christian --- I am using MySQL 3.23.49 The query that I am doing is: SELECT DISTINCT(biuserid) FROM accesslog Table definition: CREATE TABLE accesslog ( LOGID int(11) NOT NULL default '0', PAGEID varchar(10) NOT NULL default '', BIUSERID varchar(10) default NULL, DATEACCESSED date NOT NULL default '-00-00', HOST varchar(128) NOT NULL default '', OWNERID char(3) NOT NULL default '', SESSIONID varchar(20) default NULL, TIMEACCESSED time NOT NULL default '00:00:00', KEY i1 (BIUSERID) ) TYPE=MRG_MyISAM UNION=(accesslog_2002_11_04,accesslog_2002_11_05,accesslog_2002_11_06); CREATE TABLE accesslog_2002_11_04 ( logid int(11) NOT NULL default '0', PAGEID varchar(10) NOT NULL default '', BIUSERID varchar(10) default NULL, DATEACCESSED date NOT NULL default '-00-00', HOST varchar(128) NOT NULL default '', OWNERID char(3) NOT NULL default '', SESSIONID varchar(20) default NULL, TIMEACCESSED time NOT NULL default '00:00:00', KEY i1 (BIUSERID) ) TYPE=MyISAM; Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT slow after adding INDEX on MERGE table
I have 3 identical tables with approx 150,000 row each. These tables have currently no indexes. If I do a specific query on each table it takes approx 3 seconds per table. Doing the same query on the MERGE tables takes approx 8.5 seconds. I then created an index on the tables (identical index on each one...). Doing the same query on the individual tables takes about 1 second. If I then query the MERGE table (before defining the new index on the MERGE table) it takes exactly the same time to get the results (approx 8.5 seconds). Then I defined the INDEX on the MERGE table. I would expect the query to take approx 3 seconds, but not at all, the query now takes more then a 100 seconds to execute I really dont understand what is happening. Maybe there is something I am not getting right regarding indexes and MERGE tables! I added the tables definition (with the index) below. If anyone has any idea on what is wrong. Cheers, Christian --- I am using MySQL 3.23.49 The query that I am doing is: SELECT DISTINCT(biuserid) FROM accesslog Table definition: CREATE TABLE accesslog ( LOGID int(11) NOT NULL default '0', PAGEID varchar(10) NOT NULL default '', BIUSERID varchar(10) default NULL, DATEACCESSED date NOT NULL default '-00-00', HOST varchar(128) NOT NULL default '', OWNERID char(3) NOT NULL default '', SESSIONID varchar(20) default NULL, TIMEACCESSED time NOT NULL default '00:00:00', KEY i1 (BIUSERID) ) TYPE=MRG_MyISAM UNION=(accesslog_2002_11_04,accesslog_2002_11_05,accesslog_2002_11_06); CREATE TABLE accesslog_2002_11_04 ( logid int(11) NOT NULL default '0', PAGEID varchar(10) NOT NULL default '', BIUSERID varchar(10) default NULL, DATEACCESSED date NOT NULL default '-00-00', HOST varchar(128) NOT NULL default '', OWNERID char(3) NOT NULL default '', SESSIONID varchar(20) default NULL, TIMEACCESSED time NOT NULL default '00:00:00', KEY i1 (BIUSERID) ) TYPE=MyISAM; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 4 choosing wrong index for merge table
A query that took less than a second under 3.23.44 started taking minutes when we upgraded to 4.0.3. The original query involved multiple tables including one merge table containing 10 subtables and millions of rows, but I've trimmed it down to a manageable test case where the merge table has only one subtable and 10 rows. In the test case (see SQL below), there are three EXPLAIN statements. The first is for the problem query, from the merge table. Note that it incorrectly uses the index named 'files' even though the primary index included both fields from the WHERE clause. The second EXPLAIN shows the same query performed on the MyISAM table directly, without the merge. There the correct index is chosen. The third EXPLAIN shows a similar query on the merge table but selecting only one file_code value rather than a range. There the correct index is chosen again. We can work around the problem by adding USE INDEX (PRIMARY) to the queries, but this does look like a bug in the query optimization for 4.0. I hope this helps in tracking it down. - Begin SQL - CREATE TABLE files_snapshot_pm_01 ( fileset_id tinyint(3) unsigned NOT NULL default '0', file_code varchar(32) NOT NULL default '', fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) ) TYPE=MyISAM; INSERT INTO files_snapshot_pm_01 VALUES (2, '000111', 1), (2, '000112', 1), (2, '000113', 1), (2, '000114', 1), (2, '000115', 1), (2, '000116', 1), (2, '000117', 1), (2, '000118', 1), (2, '000119', 1), (2, '000120', 1); CREATE TABLE files_snapshot_pm ( fileset_id tinyint(3) unsigned NOT NULL default '0', file_code varchar(32) NOT NULL default '', fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) ) TYPE=MRG_MyISAM UNION=(files_snapshot_pm_01); # This query, with the merge table, incorrectly uses the # 'files' index: EXPLAIN SELECT * FROM files_snapshot_pm WHERE fileset_id = 2 AND file_code BETWEEN '000115' AND '000120' LIMIT 1\G # This query uses the primary index, as it should: EXPLAIN SELECT * FROM files_snapshot_pm_01 WHERE fileset_id = 2 AND file_code BETWEEN '000115' AND '000120' LIMIT 1\G # This query, with the merge table again but selecting a # specific file_code value rather than a range, uses the # correct index: EXPLAIN SELECT * FROM files_snapshot_pm WHERE fileset_id = 2 AND file_code = '000115' LIMIT 1\G - End SQL - -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
