Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>9 minutes * 60 seconds = 540 seconds</p> <p>17000 / 540 = ~ 30.5 records per second when inserting</p> <p>Because you did not post your server configuration, load, ram etc. we cannot directly put the issue at a certain point unfortunately. Anyhow 30.5 inserts / sec is not much for a serious server. It is very doable certainly because your row size doesn't seem big.</p> <p>What you need to do is do some serious measurements. For example lack of memory size will create issues. Also lots of expensive indexes on the table will slow down insertion quite hard.</p> <p>If you do this lots of times (for example by user upload) it might be wise to create a queue for it since it will anyway take some time. Though 17k inserts should be doable in seconds not minutes.</p> <p>There is a lot of documentation available about optimizing MySQL for inserts, this is a general overview of the influences at the speed: <a href="http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html</a></p> <p>At first sight it doesn't seem to be your script, that's not really special. Though I would seperate the process in 2 scripts:</p> <ol> <li>Handle the insertion in a process running in the background, like a cron, a constant running script etc. So upload the data, store it and the background process will handle that. Maybe create a table importjobs for it.</li> </ol> <p>Every x records you could do something like this:</p> <pre><code>UPDATE importjobs SET counter = :amountimported WHERE id=:jobid </code></pre> <ol> <li><p>A script giving you answer to the status. By getting the status messages written by the background script.</p> <p>SELECT counter/total AS partdone, counter, total FROM importjobs WHERE id=:jobid</p></li> </ol> <p>That way you can measure and improve the process totally seperate from the user interface. Seperation of concerns happens. You give full speed to the import and you can seperate the update indicator totally from the process.</p> <p>Depending on the speed you get you can decide whether you want to update every 5, 10, 20, 60 seconds or whatever. This lookup is quite cheap so you can do it quite some times. Mostly because that importjobs table is also very small.</p>
    singulars
    1. This table or related slice is empty.
    1. POBig Json data to mysql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USLuc Franken
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POBig Json data to mysql
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSorry about lack information.Im working at local and 8-9min not exact time.For now i am trying to get json data from an api (http://eu.battle.net/auction-data/veknilash/auctions.json) and saving as a json file.Than reading and generating sql file and i will try to load to mysql.(Not succeed yet.) Is it true way to do that or do i need any other logic for it ? if i can succeed at this part i will try to do that with progress information.
      singulars
      1. PO
      1. USxuma
    2. COThat's ok, it just shows how you can measure. For example you can write a simple script inserting lots of rows to have an indication of speed. Generally I see on my development machines a performance much less compared to optimized servers we use in production. First measure then code, otherwise you might spend lots of time on the wrong things.
      singulars
      1. PO
      1. USLuc Franken
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload