> -----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 type possible_keys key key_len ref rows Extra etg_bi_accesslog index NULL i1 11 NULL 442157 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 '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
