1

I have a database table with 900,000+ rows, 20 columns, mostly string data. Using MySQL Workbench, I want to page through the entire contents of the table to see what exactly has been inserted into the table (I am reviewing it because I believe my code somehow accidentally only partially loaded the contents of a CSV file and I want to understand why and where the process ceased loading the entire CSV file).

The problem is that, because of the somewhat large size of the DB table, running a "SELECT * FROM..." query with "Don't Limit" causes my computer to take far too long (over 10 minutes) to produce a result in MySQL Workbench's result grid and basically appears to freeze up.

Is there a way to make the MySQL Workbench result grid load much much faster? Or should I be using a different tool?

I'm a bit of a noob, so it's possible I'm doing something pretty stupid here. Thanks in advance!

Improve this question
9
  • Use limit and offset to just grab a subset of rows each time?
    – Colin 't Hart
    Commented Feb 10, 2015 at 19:04
  • @Colin'tHart Yes, perhaps that is the best way to handle the data. If I use limit and offset and only request specific columns instead of the entire table, the results grid loads significantly faster. Of course, several queries with different offsets must be ran to view the entire table, but that is workable.
    – Cato Minor
    Commented Feb 10, 2015 at 19:42
  • If you're on Linux, you can use something like "pager less" on the mysql client to page though the data. However, even at 10 pages/second, you'll be paging for 25 hours. Have fun! :-)
    – Vérace
    Commented Feb 10, 2015 at 20:14
  • @Vérace I'm using Windows 8, although I think a similar "pager less" option exists in the command line utility. That said, I agree with you that this would still be a very time consuming manner in which to review the data :).
    – Cato Minor
    Commented Feb 11, 2015 at 9:23
  • Pager doesn't work on Windows. You could try sending it to a file and use your editor of choice to peruse the file - if you have a definite known end-point for your .csv load, that might be a better strategy than paging through results. Or!! You could save your table as a .csv (with a different name to your original file) and then do a diff on the files - that might immediately show up problems?
    – Vérace
    Commented Feb 11, 2015 at 10:31

1 Answer 1

Reset to default
0

The first suggestion was to use the "pager less" command - but as pointed out, even at 10 pages a second, it would take 25 hours to get through 900,000 records.

Unfortunately, the "pager" command doesn't work on Windows. It was then suggested to send the output to a file and scroll through that - looking for a disconnect between the .csv input and the final table.

Then, this was proposed

    Or!! You could save your table as a .csv (with a different name  
to your original file) and then do a diff on the files - that might 
immediately show up problems?

This appears to have resolved the issue for the OP.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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