executing single query in loop increase time every time

Question

Am executing one query which is the same as below. Now here the problem is as limit increasing its increasing execution of time.

I am adding limit using a PHP script. So anyone likes to suggest to stop increasing the time of execution by mysql?

There is total records are 12042993 which I have to copy from one table to another table. But when am executing the single query without limit that time it's not working.

So will you please suggest a better way to avoid this increasing time?

For now, I have added one index which is below. I know its wrong. But for me, I just have to concentrate only and only on this query. There is no any other query is running on the entire server/database.

alter table cdrs add index cdrs_data  (`callstart`,`uniqueid`,`accountid`,
    `type`,`callerid`,`sip_from_uri`,`callednum`,`billseconds`,trunk_id,disposition,
    debit,cost,provider_id,pricelist_id, package_id,pattern,notes,reseller_id,
    provider_call_cost, call_direction,calltype,progress_media_stamp,
    progress_mediamsec, is_recording,call_reward_amount);

insert into cdrs_data(uniqueid,accountid,type,callerid,sip_from_uri,callednum,
    billseconds,trunk_id,disposition,callstart,debit,cost,provider_id,pricelist_id,
    package_id,pattern,notes,reseller_id,provider_call_cost,call_direction,
    calltype,progress_media_stamp,progress_mediamsec,is_recording,call_reward_amount)
  select  uniqueid,accountid,type,callerid, sip_from_uri, callednum,billseconds,
       trunk_id,disposition, callstart,debit,cost, provider_id,pricelist_id,package_id,
       pattern,notes,reseller_id,provider_call_cost, call_direction,calltype,
       progress_media_stamp,progress_mediamsec, is_recording,call_reward_amount 
    from callinfo 
    where callstart >= '2017-01-30 00:00:00' and callstart < '2019-05-06 00:00:00' 
    order by callstart asc limit 24000,3000

All suggestions are appriciated.

Answer 1

What percentage of the 12M rows are you transferring?

This gets slower and slower as you increase the OFFSET:

limit 24000,3000

Instead, "remember where you left off". More: http://mysql.rjweb.org/doc.php/pagination and http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

Is there an index on callstart?

The INDEX cdrs_data is a terribly long index. The only way it might be useful is if it is "covering".

The TABLE cdrs_data -- does it have any indexes? Of so, disabling the indexes before starting the inserts is likely to speed things up. Reenable afterward.

Another option is to use mysqldump with a --where clause. for the dump side; then mysql for loading in the new place.