Re: MyISAM Index corruption
definately typo 1.6 million rows / day is the real number
>Is "1.6 lines per day" a typo? This seems to be a very low rate.
####################################################################
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
4582 mysql 15 0 247M 226M 2692 S 3.1 3.9 82:21 2 mysqld
>What does the 'VIRT' column show for mysqld before and after the query?
####################################################################
Pretty much all my queries are plagued with "Copying to tmp table" status. and the data return is very slow
I have the inserts happenning from a perl script:
INSERT INTO RATEDCDR(UNIQUEID,BILLABLEFLAG,COMPANYID,DURATION,DIALEDNUM,DESTDESC,ORIGDESC,
SRC_FILE,CALLTYPE,ORIGIP,ORIGGROUP,TERMIP,TERMGROUP,DISCONNECTREASON,DESTINATIONCOUNTRY,
ORIGCOUNTRY,BUYERID,BUYRATE,BUYROUNDEDSEC,BUYPRICE,BUYCOMMIS,SELLID,SELLRATE,SELLROUNDEDSECONDS,SELLPRICE,SELLCOMMIS,
CREATED,DATE_TIME,BUYPLANID,BUYROUTEGROUPID,SELLPLANID,SELLROUTEGROUPID,MANIPDIALED,ORIG_MATCHED,TERM_MATCHED)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?)
then i have many selects that are used for reporting
SELECT DATE_FORMAT(RATEDCDR.DATE_TIME,'%Y-%m-%d %H') - INTERVAL 0.0 HOUR ,
COUNT(RATEDCDR.CALLTYPE) AS CALLCOUNT,
SUM(RATEDCDR.SELLROUNDEDSECONDS) AS SECONDS,
SUM(RATEDCDR.BUYPRICE) AS BUYPRICE,
SUM(RATEDCDR.SELLPRICE) AS SELLPRICE,
SUM(RATEDCDR.BUYCOMMIS + RATEDCDR.SELLCOMMIS) AS FEES,
SUM(RATEDCDR.SELLPRICE - RATEDCDR.BUYPRICE - RATEDCDR.BUYCOMMIS - RATEDCDR.SELLCOMMIS) AS PROFIT
FROM RATEDCDR
WHERE
RATEDCDR.DATE_TIME BETWEEN '2006-04-03 00:00:00' - INTERVAL 0.0 HOUR
AND '2006-04-03 23:59:59' - INTERVAL 0.0 HOUR
AND RATEDCDR.SELLID != 'IPSono'
GROUP BY DATE_FORMAT(RATEDCDR.DATE_TIME,'%Y-%m-%d %H') - INTERVAL 0.0 HOUR
ORDER BY DATE_FORMAT(RATEDCDR.DATE_TIME,'%m-%d-%Y %H') DESC
the explain on the above query is:
|id|select_type|table |type |possible_keys |key |key_len|ref |rows |Extra|
|1 |SIMPLE |RATEDCDR|range|INDX_RATED_DATE|INDX_RATED_DATE|8 |NULL|846114|Usingwhere;Usingtemporary;Usingfilesort|
>And what does your query do? Is it a join? Will it use temporary table
>(s) for execution?
Edited 1 time(s). Last edit at 04/03/2006 09:58AM by shiraz khalid.