Skip to content
Advertisement

SQL script behaves differently when run from PHP

I have about 5 million records in a database table (MySQL 5.6), and I wanted to get the last 2 dates, grouped by ID. Looking around the web, I found examples that allowed me to cobble together the following…

Running this code from MySQL Workbench, returned 5,700 rows, which is what I expected. I then tried to call this SQL as-is from PHP, using the following SQL string…

However, running this code resulted in out-of-memory. I them modified the code to return a count of the number of records expected, and instead of the expected 5,700 rows, it returned 4,925,479. So my question is “what do I have to change in my PHP SQL above, to get the correct results that I was getting from MySQL Workbench”.

Advertisement

Answer

Assigning variables in MySQL has been deprecated. You should be using window functions:

Even in older versions your code is not guaranteed to work because MySQL does not guarantee the order of evaluation of expressions. You are assigning the variable in one expression and using it in another — and those can go in either order.

If your dates are unique per id, you can do this without variables:

For performance, you want an index on data(id, date).

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement