Mysql – How to force feed offset keyword a number calculated from table

MySQL

I am trying something as shown here.

As in this sql fiddle http://sqlfiddle.com/#!9/8d217/4 I created table

CREATE TABLE something (`some_column` int);
INSERT INTO something (`some_column`) VALUES (10),(20),(30),(40),(50);

I am trying to find median value so following is the query

select some_column from something order by some_column limit 1 offset 
(select floor(count(some_column) / 2) from something);

MySQL complains about the query part after offset; and it does accept hard coded values like any integer instead of sub-query like (select floor(count(some_column) / 2) from something).

I am guessing the offset expects to get hard coded value and not a column with a value. I did try other options like using variables @ but cannot make it work.

My question is; if I want to use a value from query operation in offset or limit and not a hard coded value, how should I go about it?

Best Answer

Plan A: Write a stored procedure to construct (via CONCAT()) the query you need, then use prepare and executed.

Plan B: Create a temp table with an AUTO_INCREMENT column in order to number the rows. Then look in that table for the answer.

Plan C: Use @variables to count through the table.

Plan D: http://www.artfulsoftware.com/infotree/queries.php#444

YOUR ACTUAL QUESTIONS

Does this actually do the same or is my understanding wrong?

Is one slower/faster in larger tables

Since both queries are the same, there is no difference

Does the result of offset change when I do WHERE column=1 (say column has >100 different values)

Does the result of offset change when I do ORDER BY column ASC (asuming it has random values)

Using LIMIT does not change any result sets. They simply navigate within the result set.

This query

SELECT id,name,description FROM tablename ORDER BY id LIMIT 1000,25

would be different from

SELECT * FROM (SELECT id,name,description FROM tablename LIMIT 1000,25) A ORDER BY id;

because the LIMIT is being applied at a different stage.

The first query returns nothing if tablename has less 1000 rows

The second query returns nothing if the subquery has less 1000 rows

CONCLUSION

You will have to sculpt the query to make sure you are sorting data at the right stage

MySQL – Using OFFSET with GUID IDs

Don't use offset - it has to read all the rows to skip them.

If you order rows for pagination by created_at, then thats the column you need to use.

First page:

SELECT ... FROM table
ORDER BY created_at, id
LIMIT N + 1;

The +1 gives you a peek to the next page - you can quickly decide if next page exists and where does it start. If created_at is not guaranteed to be unique, adding the ID to the ordering helps keep it stable.

SELECT ... FROM table
WHERE created_at >= <(x+1)th created_at from previous page>
    AND id >= <(x+1)th id from previous page>
ORDER BY created_at, id
LIMIT N + 1;

You use the extra item from fetching the previous page to quickly find the start of current page using the ordering index to get new page.

Best Answer

Related Solutions

MySQL – Limit 1000,25 vs Limit 25 Offset 1000

YOUR ACTUAL QUESTIONS

CONCLUSION

MySQL – Using OFFSET with GUID IDs

Related Question