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`,
    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,
  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.

  • Why are you using a PHP script? When you say doing it as a single query without LIMIT didn’t work, what do you mean? We’re you executing that from PHP? Execute the query without LIMIT from the MySQL command line - be patient, leave it until it finishes. Let us know if that errors. PHP scripts have a timeout that you have probably hit – Philᵀᴹ May 11 '19 at 10:34
  • Why are you having an index? You do not need it, adding it after loading all the records does speed up the loading of the records. – Luuk May 11 '19 at 12:14

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.