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