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]
