1. This website uses cookies to improve service and provide a tailored user experience. By using this site, you agree to this use. See our Cookie Policy.
    Dismiss Notice

update large MySQL database by rows

Discussion in 'Scripting' started by shaymiller, Jul 18, 2019.

  1. shaymiller

    shaymiller Junior Member

    Joined:
    Dec 3, 2016
    Messages:
    155
    Likes Received:
    8
    I have mysql database with 24K rows. How can I update first 100 rows then next 100, until full database is updated

    The way I do it now, it by using simple update query directly in phpmyadmin below. It takes 5 mins+ and some times it crashes

    Update tracks
    Inner join ol_vidviews ON (tracks.name = ol.vidviews.name)
    SET tracks.views = ol_vidviews.post_id

    Can someone post php code to do this?
     
  2. MrAttribution

    MrAttribution Junior Member

    Joined:
    Jun 16, 2019
    Messages:
    103
    Likes Received:
    38
    24k rows is nothing for database. phpmyadmin is an issue here.

    Send SQL query directly to the database.
     
  3. shaymiller

    shaymiller Junior Member

    Joined:
    Dec 3, 2016
    Messages:
    155
    Likes Received:
    8

    How do I send query to database?

    For now 24K rows, but will go up as I more data plus I will be updating 24K rows weekly basis
     
  4. MrAttribution

    MrAttribution Junior Member

    Joined:
    Jun 16, 2019
    Messages:
    103
    Likes Received:
    38
    You need a client software. Default ones are command line clients:
    MySQL: https://dev.mysql.com/doc/refman/5.7/en/mysql.html
    Postgres: https://www.postgresql.org/docs/current/app-psql.html

    But it's a learning curve.

    You can use something easier with graphical interface, like https://www.heidisql.com/ or http://www.sequelpro.com/

    phpMyAdmin is actually database client with graphical interface, but it's very slow. Try Heidi or Sequel, it should work for your amount of data.
     
  5. shaymiller

    shaymiller Junior Member

    Joined:
    Dec 3, 2016
    Messages:
    155
    Likes Received:
    8

    Sorry for the late follow-up

    I was hoping for not to use a client software and the admin CP I am building is all online. Isn't there a php code or javascript code to update mysql step by step?
     
  6. Diplomat

    Diplomat Jr. VIP Jr. VIP

    Joined:
    Oct 25, 2011
    Messages:
    1,276
    Likes Received:
    728
    Home Page:
    24k rows should get updated in seconds (max) not minutes. Use MySQL cli instead.. it won't crash on you.
     
  7. davvv

    davvv Registered Member

    Joined:
    Mar 28, 2016
    Messages:
    72
    Likes Received:
    18
    Use something like MySQL Workbench: https://www.mysql.com/products/workbench/

    I'm guessing you have the host details (IP/Username/Password) so you can set up a connection using mySQL Workbench and run the query (should take a few minutes to install and set up a connection).

    Way better than phpmyadmin and if you want to do some PHP/mySQL work, could be worth moving away from phpmyadmin and using a separate mySQL client
     
  8. shaymiller

    shaymiller Junior Member

    Joined:
    Dec 3, 2016
    Messages:
    155
    Likes Received:
    8

    Its a wordpress blog self-hosted. I don't know much about workbench.

    I came across this website. https://www.plus2net.com/php_tutorial/ajax-progress2.php

    maybe that might work.

    I am surprised there is no code online to use.
     
  9. davvv

    davvv Registered Member

    Joined:
    Mar 28, 2016
    Messages:
    72
    Likes Received:
    18
    I'm not too sure on your DB structure but an update query on 24k rows shouldn't take over 5 mins so could be worth double checking your query to see if it can be optimised (do you need an Inner Join and can you link the tables based on ID's rather than names).

    When you say self hosted, does that mean that you're using a service that sets up the Wordpress installation for you so you don't need to install anything, so don't have access to the DB directly (hostname & DB credentials)?

    If it takes a long time to run but doesn't timeout, could be worth just chucking your mySQL query into a PHP file, upload it to the server (if you have access) and then run it through the night to make the necessary updates.

    Once done, remove your PHP file from the server

    If you need help setting up a script to connect to your DB and run a query, the following link can help: https://www.a2hosting.co.uk/kb/developer-corner/mysql/connect-to-mysql-using-php


    Also, could be worth adding your question to https://stackoverflow.com/, you'll get answers from developers who can possibly answer your query quicker (as I'm not too sure how many PHP/mySQL developers there are on this forum)