Try orgainzing the date and time indexes as follows: alter table logs_2006_11 drop index date; alter table logs_2006_11 drop index time; alter table logs_2006_11 add index date_time (date,time);
You at least want a index range scan across dates in log_2006_11 When you use a LEFT JOIN you must walk across all 3302388 rows in cs_documents as the EXPLAIN says This is another reason why a temp table comes into being Change the query so that you pick up the desired date range first before performing the join to cs_documents select B.str_url from (select int_url_strem,int_cs_ip from logs_2006_11 where date >= '2006-11-01'and date <= '2006-11-05' and int_bots = 0) A,cs_documents B where A.int_url_strem = B.id And ( B.str_url LIKE '%.asp' Or B.str_url LIKE '%.php' Or B.str_url LIKE '%.html' Or B.str_url LIKE '%.htm' Or B.str_url LIKE '%.jsp' Or B.str_url LIKE '%.aspx' Or B.str_url LIKE '%.tpl' Or B.str_url LIKE '%.cgi' Or B.str_url LIKE '%/' ) and B.str_url NOT LIKE '/phpadsnew/%' ; Please note I took out the time out of the where clause since only the date range matters. Yet, if you need the URLs to appear in order by date and time, then the query should look more like this: select B.str_url from (select int_url_strem,int_cs_ip,date,time from logs_2006_11 where date >= '2006-11-01'and date <= '2006-11-05' and int_bots = 0) A,cs_documents B where A.int_url_strem = B.id And ( B.str_url LIKE '%.asp' Or B.str_url LIKE '%.php' Or B.str_url LIKE '%.html' Or B.str_url LIKE '%.htm' Or B.str_url LIKE '%.jsp' Or B.str_url LIKE '%.aspx' Or B.str_url LIKE '%.tpl' Or B.str_url LIKE '%.cgi' Or B.str_url LIKE '%/' ) and B.str_url NOT LIKE '/phpadsnew/%' group by A.int_cs_ip order by A.date,A.time ; Give these a try. Let me know how it works out. ------------------------------------------------------------ Select cs_documents.str_url FROM logs_2006_11 LEFT Join cs_documents ON int_url_strem = cs_documents.id where logs_2006_11.int_bots = 0 And ( cs_documents.str_url LIKE '%.asp' Or cs_documents.str_url LIKE '%.php' Or cs_documents.str_url LIKE '%.html' Or cs_documents.str_url LIKE '%.htm' Or cs_documents.str_url LIKE '%.jsp' Or cs_documents.str_url LIKE '%.aspx' Or cs_documents.str_url LIKE '%.tpl' Or cs_documents.str_url LIKE '%.cgi' Or cs_documents.str_url LIKE '%/' ) And logs_2006_11.int_bots = 0 And ( cs_documents.str_url NOT LIKE '/phpadsnew/%' ) And logs_2006_11.date >= '2006-11-01' And logs_2006_11.date <= '2006-11-05' And logs_2006_11.time >= '00:00:00' And logs_2006_11.time <= '23:59:00' Group By logs_2006_11.int_cs_ip ----- Original Message ----- From: Francis <[EMAIL PROTECTED]> To: Rolando Edwards <[EMAIL PROTECTED]> Cc: MySQL General <mysql@lists.mysql.com> Sent: Monday, November 6, 2006 9:58:23 AM GMT-0500 US/Eastern Subject: RE: " Copy to tmp table " optimize id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'logs_2006_11', 'ALL', 'date,time', '', , '', 3302388, 'Using where; Using temporary; Using filesort' 1, 'SIMPLE', 'cs_documents', 'eq_ref', 'id', 'id', 3, 'trafix_sites_00026.logs_2006_11.int_url_strem', 1, 'Using where' -----Original Message----- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 9:54 AM To: Francis Cc: MySQL General Subject: Re: " Copy to tmp table " optimize As for this part of your query ( cs_documents.str_url LIKE '%.asp' Or cs_documents.str_url LIKE '%.php' Or cs_documents.str_url LIKE '%.html' Or cs_documents.str_url LIKE '%.htm' Or cs_documents.str_url LIKE '%.jsp' Or cs_documents.str_url LIKE '%.aspx' Or cs_documents.str_url LIKE '%.tpl' Or cs_documents.str_url LIKE '%.cgi' Or cs_documents.str_url LIKE '%/' ) and cs_documents.str_url NOT LIKE '/phpadsnew/%' These will cause table scans Try running EXPLAIN on the SQL statement to verify row counts on all intermittent steps ----- Original Message ----- From: Francis <[EMAIL PROTECTED]> To: Rolando Edwards <[EMAIL PROTECTED]> Cc: MySQL General <mysql@lists.mysql.com> Sent: Monday, November 6, 2006 9:31:32 AM GMT-0500 US/Eastern Subject: RE: " Copy to tmp table " optimize Hi This is my SQL command: Select cs_documents.str_url FROM logs_2006_11 LEFT Join cs_documents ON int_url_strem = cs_documents.id where logs_2006_11.int_bots = 0 And ( cs_documents.str_url LIKE '%.asp' Or cs_documents.str_url LIKE '%.php' Or cs_documents.str_url LIKE '%.html' Or cs_documents.str_url LIKE '%.htm' Or cs_documents.str_url LIKE '%.jsp' Or cs_documents.str_url LIKE '%.aspx' Or cs_documents.str_url LIKE '%.tpl' Or cs_documents.str_url LIKE '%.cgi' Or cs_documents.str_url LIKE '%/' ) And logs_2006_11.int_bots = 0 And ( cs_documents.str_url NOT LIKE '/phpadsnew/%' ) And logs_2006_11.date >= '2006-11-01' And logs_2006_11.date <= '2006-11-05' And logs_2006_11.time >= '00:00:00' And logs_2006_11.time <= '23:59:00' Group By logs_2006_11.int_cs_ip cs_documents ( id mediumint(5) NOT NULL auto_increment, str_url varchar(100) NOT NULL default '', UNIQUE KEY id (id), KEY iurl (str_url), FULLTEXT KEY turl (str_url) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 logs_2006_11 ( id int(8) unsigned NOT NULL auto_increment, int_cs_ip mediumint(6) unsigned NOT NULL default '0', date date NOT NULL default '0000-00-00', time time NOT NULL default '00:00:00', int_url_strem mediumint(5) unsigned NOT NULL default '0', int_url_query mediumint(7) unsigned NOT NULL default '0', int_status enum('200','206','301','302','304','400','403','404','406','416','500',' 501','502') NOT NULL default '200', int_size_client mediumint(7) unsigned NOT NULL default '0', int_size_server int(8) unsigned NOT NULL default '0', int_referrer mediumint(6) unsigned NOT NULL default '0', int_user_agent smallint(5) unsigned NOT NULL default '0', int_search tinyint(3) unsigned NOT NULL default '0', int_platforms smallint(5) unsigned NOT NULL default '0', int_bots tinyint(3) unsigned NOT NULL default '0', int_host tinyint(3) unsigned NOT NULL default '0', UNIQUE KEY `id` (`id`), KEY ip (`int_cs_ip`), KEY date (`date`), KEY time (`time`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -----Original Message----- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Monday, November 06, 2006 9:02 AM To: Francis Cc: MySQL General Subject: Re: " Copy to tmp table " optimize You need to look at the GROUP BY clause If all the columns from a GROUP BY clause are not indexed or if the columns in the GROUP BY clause are from different tables, MySQL will use the temp table. Please send your query to the MySQL List and the structure of all tables in the query ----- Original Message ----- From: Francis <[EMAIL PROTECTED]> To: MySQL General <mysql@lists.mysql.com> Sent: Monday, November 6, 2006 8:50:40 AM GMT-0500 US/Eastern Subject: " Copy to tmp table " optimize Hi list, An others question, I am working on projet and i made some test. I use large databases, some table go up to 1.8g. The problem we have is wend I try to select data in table whit a group by function and I got " Copy to tmp table " This function take a long time to execute. I check on my server for the cpu usage and no hang up, no hang up on hardrive, the only hang up I have is on memory. 1 Question, the " copy to tmp table " function can optimize to get faster ? 2 Question, what is the max table I can create in databses ? because, the way I found to optimize " copy to tmp table " is by spliting the data in my table in multiple tables to lower the size...But if I do this way, the table counts on my database can go up to 600 tables :S I don't think is a good way to do this. Ty for your help and sorry for my english... Francis ! -- 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] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
