2

Seeing as that MySQL/PDO does not support scrollable cursors, is there any other way to advance the cursor without fetching the data, and without using OFFSET?

I basically need to equivalent of mysql_data_seek. I know there's an existing question that's basically the same but the answers there recommend fetching the rows to advance the cursor, or to use PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL which doesn't work.

I've tried fetching the rows, but it's causing massive slowness on my server because MySQL is sending way more data than it needs to.

I just want to confirm or deny the existence of a method to advance the cursor before I spend many hours rewriting dozens of queries in a legacy app.

Community
  • 1
  • 1
mpen
  • 272,448
  • 266
  • 850
  • 1,236
  • Thanks Jay. Much appreciated. The checked answer there is flat out wrong. – mpen Apr 03 '17 at 19:57
  • The bug you point to is *over 12 years old* - so how is this wrong? Have you taken the time to test it, or are you running an older version of PHP where the bug is not fixed? – Jay Blanchard Apr 03 '17 at 20:04
  • 1
    "MySQL is sending way more data than it needs to." MySQL only sends what it's asked to send. If you're doing `SELECT * FROM gigantotable` then you're going to get slammed. What about `SELECT x,y FROM gigantotable LIMIT 500`? – tadman Apr 03 '17 at 20:56
  • @tadman Problem is that it's a legacy app that previously used `mysql_*` which supported `mysql_data_seek`. We've just converted it to PDO, but I don't want to go through 1000s of queries rewriting them to use LIMIT+OFFSET. – mpen Apr 03 '17 at 21:19
  • @JayBlanchard Yes, I have tested it. I foolishly assumed that `PDO::FETCH_ORI_ABS` would work as advertised, but it doesn't seek at all. We're running PHP 5.6 at the moment. That php.net bug was closed as "Not a bug" so I assume they never will fix it. Just because it's old, doesn't mean it's no longer valid -- I've seen plenty. – mpen Apr 03 '17 at 21:21
  • No doubt @mpen, I just did not have the opportunity to run tests either. I will reopen your question. – Jay Blanchard Apr 03 '17 at 21:31
  • Wouldn't the appropriate course of action be to put a bounty on the old question looking for a more accurate/current answer? Have you tried unbuffered queries? http://stackoverflow.com/questions/6895098/pdo-mysql-memory-consumption-with-large-result-set – miken32 Apr 03 '17 at 21:42
  • @mpen PHP 7 removed `mysql_query` and associated functions, so you're sort of committed to removing it no matter what you do. Running an antiquated version of PHP to work around this problem is extremely risky. I think trimming down what you're fetching side-steps the cursor issue. Honestly, I've never encountered a problem that *required* them and I've done a lot of database work over the last few decades. I stopped using them around the time of dBase III. – tadman Apr 03 '17 at 22:16
  • @miken32 I did add a bounty to the old one. Don't think unbuffered queries will help me here. It'd reduce the memory footprint, but I'd still be fetching and discarding rows that I never use. Moreover, I think it'd be too hard to make work; we do other queries in-between and MySQL doesn't like running more queries while you have open cursors. – mpen Apr 03 '17 at 22:35
  • @tadman Indeed. This is the primary reason we're *finally* trying to get rid of `mysql_query`, so we can upgrade to PHP 7. And *this* is why we've been putting it off for so long. Seeking certainly isn't *necessary*, it just saves us from rewriting 1071 chunks of code where this was done. – mpen Apr 03 '17 at 22:38
  • That sounds like 1071 refactoring opportunities. It seems odd that you have that many places where that's done, it sounds like rampant duplication of code, which could probably be consolidated and cleaned up considerably. Best of luck, though. Hope it works out. – tadman Apr 03 '17 at 22:43
  • 1
    @tadman Hahaha.. that's exactly what this is (copy-pasta). Oh joy. Three of us are going to spend the rest of the afternoon on this. Oh well, needed to be done sometime I guess. – mpen Apr 03 '17 at 22:52

1 Answers1

-3

is there any other way to advance the cursor without fetching the data, and without using OFFSET?

You don't need it.

MySQL is sending way more data than it needs to.

Mysql has nothing to do here. It's your SQL requesting way more data than it needs to.

Change your sql instead of looking for the useless feature that won't help you anyway.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345