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 '0000-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 '0000-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
