Menu
I've got a webpage which displays webcomics. There are currently 1622 pages. I need to display the current comic page (I have its ID), and the links to the first, previous, next and last pages. The ordering is also non-trivial (there's a long ORDER BY due to quirky DB design - legacy stuff), so I can't do stuff like "where ID=1" either.
So, the question is easy - I know the ID of a record. I do a SELECT ... FROM ... WHERE ... ORDER BY ... query and want to retrieve the first record, the last record, the record with the ID I know of, and the records directly preceding and succeeding the record with the known ID.
The unfiltered query returns over 1600 rows, and there's a new row every day. The query will be run several times per second (there's a fair readership). What's the most efficient way? Is there anything better than the naive "get all rows and filter out what I need in PHP code"? Note that I know I can cache the result on PHP-side, but I was wondering if there is some MySQL-related optimization available here.
Added: One solution is to do several queries - one for each required value. I should have said that I know of it and was thinking of something more elegant.
206
This one-liner is the simplest query in the list, to get the last 3 number of records in a table. The TOP clause in SQL Server returns the first N number of records or rows from a table. Applying the ORDER BY clause with DESC, will return rows in descending order. Hence, we get the last 3 rows.
With the help of the SQL count statement, you can get the number of records stored in a table.
To get the first and last record, use UNION. LIMIT is also used to get the number of records you want.
Step 1: Drag and drop ports from source qualifier to two rank transformations. Step 2: Create a reusable sequence generator having start value 1 and connect the next value to both rank transformations. Step 4: Make two instances of the target. Step 5: Connect the output port to target.
Why not add an OrderedID column that is sequential and ordered in the proper way? You could initially populate using your complex query, then keep it updated as new pages are added.
If modifying the existing table is not an option, you could create a new table with just two columns, a FK that points to your pages table, and a OrderedID column like above?
That means for any page ID=X, you would need 1, X-1, X, X+1, and Max() - only Max() actually requires a query, and that would only change once per day. The others could just be computed.
124
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
