How to insert large amounts of data with PHP/MySQL?

Question

I am currently coding a gambling system for a client, effectivly, the way the system works is as follows:

Tickets are what we create
Each ticket will get assigned a certain amount of numbers per ticket
There are a total of 1,000,000 which are ordered randomly from 000,000 to 999,999
These 1 million numbers get split into tickets the amount of numbers per ticket is defined by the admin.

Now the problem is at the moment we have an array of 1 million numbers sorted randomly, this is fine, but what would be the best way to insert this into a database?

We have two tables

tickets

ID
ticketUID
prizeID
addedDate

ticketNumbers

ID
ticketID
number

At the moment when the user decides to add the tickets to the prize we loop through and create the total tickets which is determined by the total amount of numbers per ticket, e.g. 1 number per ticket = 1 million tickets, etc...

Now we loop through check the total numbers per ticket add how ever many from our number array then remove it from the original array so there are no duplicate numbers.

Now where the problem stems from is that this takes quite a lot of memory in this way and it causes the page to time out....

Is there another way someone can suggest to help overcome this problem?

P.S. I cannot provide source code, but this is essentially what we are doing, and what i really need is theory of other methods of how i could handle the insertion of all this data.

Thanks in advance

What does your insert look like now? Obscure as necessary, but seeing how you're doing it now is useful. INSERT INTO tickeNumbers (ID, tickedtID) VALUES ('$tid', $currnum), ('$tid', $currnum), ('$tid', $currnum), (...) doesn't seem terribly inefficient. — Jared Farrish
– Jared Farrish, Commented Jul 28, 2012 at 16:47
Hi there yes, that's why i Need the advise as it seems very inefficient but not sure how else I can insert this vast amount of data, we essentially loop through and create however many tickets as mentioned above then loop through however many numbers need to be inserted into each ticket and do it meaning if there is one number per ticket we insert 1 million tickets, then insert 1 million numbers... — André Figueira
– André Figueira, Commented Jul 28, 2012 at 16:49
If you're more or less doing several hundred thousand individual queries or whatever, in a loop, in PHP... Where do you think the inefficiency is occurring? — Jared Farrish
– Jared Farrish, Commented Jul 28, 2012 at 17:03
I assume in the way we are actually adding it, in the fact it gets run directly off a form, as the gentleman below mentioned a good way of doing it would be by splitting it into a separate thread, so this is what I will be doing. — André Figueira
– André Figueira, Commented Jul 28, 2012 at 17:16
Sure, you can setup a cron and offload it elsewhere. You have to be careful not to assume it's immediately available (and PHP is not multithreaded). My original thought was write out to a file and process the file later, but first, you need to know where the bottleneck(s) exist. If you're looping and looping and looping and still inserting a couple hundred thousand queries individually, what makes you think you won't hit timeouts on that script running elsewhere? You need to know what the actual problem is, not just offload it elsewhere. Log any output, too. — Jared Farrish
– Jared Farrish, Commented Jul 28, 2012 at 17:29

Joren Van Severen · Accepted Answer · 2012-07-28 16:56:09Z

1

I'm not really into gambling, so not really sure in which way these tickets have anything to do with gambling.

That said, if you really need to create such an amount of data, there are a few options. You could for example move the ticket-creating away from the user. What I mean with that is whenever the user decides to 'add the tickets to the prize' you delegate this task to another process/thread and let the user move on. Then when the creation of the tickets is done, the process notifies the user somehow.

answered Jul 28, 2012 at 16:56

Joren Van Severen

2,3692 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

André Figueira Over a year ago

Thanks that's actually really helpful, it's PHP, and the tickets here are created by the admin, the users then buy tickets and the prize is given away as a lottery.

rita · Accepted Answer · 2012-07-28 16:57:16Z

1

I can't think of a best solution here at the moment without trying stuff - do you actually have to save the numbers in the first place in a database? If its plain numbers from 000000 to 999999 then you can just generate them on the fly, that could be more efficient.

In order to generally overcome the timeout issue you could always increase the default php timeout or disable completely for this specific task:

//set php script timeout, 0 to disable 
set_time_limit(0); 

// your time consuming code 

//don't forget to reset to 30 seconds. 
set_time_limit(30);

answered Jul 28, 2012 at 16:57

rita

5451 gold badge3 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to insert large amounts of data with PHP/MySQL?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related