Karthik,
I have included some of the parts of your original posting.
You may need to do some experiments, but these actions may help you
greatly. You may also want to consider the book "High Performance
MySQL", it has helped us a lot:
1. Make sure you have indexes on any column involved in a
search/join.
2. Convert varchar fields to just char if possible in an attempt
to get fixed length rows. This makes MySQL's job of indexing a LOT
easier and searching much FASTER.
3. The InnoDB tablespace (15 Gig) always grows and never gets
smaller -- just moving the data can kill your system. This may
be hard but you may want to do a mysql_dump and recreate the innodb
info using:
innodb-file-per-table (dedicates individual file to each table, still
overhead in ibdata, but not as much).
4. Obviously, you need FAST disks and a LOT of RAM for such large
datasets.
Hope this helps!
John
> We have 4 tables in which we have approximately 40 Million records
> per month. We are having trouble getting results from MySql as it
> takes about 4-5 hours to complete for each query. We are using this
> primarily for reporting purposes.
> varchar(3999)
> varchar(3999)
> varchar(3999)
> varchar(3999)
> varchar(3999)
> innodb_data_file_path=ibdata1:15G:autoextend
> Currently, SMAS table has 40Million records and our query takes 5
> hours to execute.
> My query is as follows on SMAS table
> select campaignName, siteName, adnetwork,date_format(loggingTime
,'%d/%m/%Y') logDate, count(distinct requestid) adpage
from SMAS where sucess = 1 GROUP BY 1,2,3,4
--
John
___________________________________________________________________
John Murtari Software Workshop Inc.
[EMAIL PROTECTED] 315.635.1968(x-211) "TheBook.Com" (TM)
http://www.thebook.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
