Geek Answers Handbook

Exact pagination with left links

I thought about this for a while, and he came to the point where I think it is better to ask and listen to what other people think.

I am creating a system that stores locations in Mysql. Each location has a type, and some locations have multiple addresses.

The tables look something like this:

location - location_id (autoincrement) - location_name - location_type_id location_types - type_id - type_name (For example "Laundry") location_information - location_id (Reference to the location table) - location_address - location_phone

So, if I wanted to query the database for the last 10 added, I would go with something like this:

 SELECT l.location_id, l.location_name, t.type_id, t.type_name, i.location_address, i.location_phone FROM location AS l LEFT JOIN location_information AS i ON (l.location_id = i.location_id) LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id) ORDER BY l.location_id DESC LIMIT 10

Correctly? But the problem is that if the location has more than 1 address, the restriction / pagination will not coincide, unless I have "GROUP BY l.location_id", but this will only show one address for each place .. what happens to the places having multiple addresses?

So, I thought that the only way to solve this is to make a request inside the loop. Something like this (pseudo code):

 $db->query('SELECT l.location_id, l.location_name, t.type_id, t.type_name FROM location AS l LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id) ORDER BY l.location_id DESC LIMIT 10'); $locations = array(); while ($row = $db->fetchRow()) { $db->query('SELECT i.location_address, i.location_phone FROM location_information AS i WHERE i.location_id = ?', $row['location_id']); $locationInfo = $db->fetchAll(); $locations[$row['location_id']] = array('location_name' => $row['location_name'], 'location_type' => $row['location_type'], 'location_info' => $locationInfo); }

Now I get the last 10 places, but by doing this, I complete at least 10 more requests, and I don’t think this helps the application performance.

Is there a better way to achieve what they need? (exact pagination).

+7
source share
4 answers

Here is your original request

 SELECT l.location_id, l.location_name, t.type_id, t.type_name, i.location_address, i.location_phone FROM location AS l LEFT JOIN location_information AS i ON (l.location_id = i.location_id) LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id) ORDER BY l.location_id DESC LIMIT 10

You are paginating for the last time. If you reorganize this request, you can paginate earlier.

 SELECT l.location_id, l.location_name, t.type_id, t.type_name, i.location_address, i.location_phone FROM (SELECT location_id,location_type_id FROM location ORDER BY location_id LIMIT 10) AS k LEFT JOIN location AS l ON (k.location_id = l.location_id) LEFT JOIN location_information AS i ON (k.location_id = i.location_id) LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id) ;

Note. I created a subquery called k . 10 keys receive and order FIRST !!!

JOINs can then continue from there, hoping using only 10 location_ids.

What helps the subquery k is an index that contains location_id and location_type_id

 ALTER TABLE location ADD INDEX id_type_ndx (location_id,location_type_id);

Here is what you might like about this approach.

How do you request the following 10 identifiers (ids 11 - 20)? Like this:

 SELECT l.location_id, l.location_name, t.type_id, t.type_name, i.location_address, i.location_phone FROM (SELECT location_id,location_type_id FROM location ORDER BY location_id LIMIT 10,10) AS k LEFT JOIN location AS l ON (k.location_id = l.location_id) LEFT JOIN location_information AS i ON (k.location_id = i.location_id) LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id) ;

All you have to do is change the LIMIT in subquery k with each new page.

  • LIMIT 20,10
  • LIMIT 30,10
  • etc.

I can improve refactoring by deleting the location table and getting the subquery k, follow these fields:

 SELECT k.location_id, k.location_name, t.type_id, t.type_name, i.location_address, i.location_phone FROM (SELECT location_id,location_type_id,location_name FROM location ORDER BY location_id LIMIT 10,10) AS k LEFT JOIN location_information AS i ON (k.location_id = i.location_id) LEFT JOIN location_types AS t ON (k.location_type_id = t.type_id) ;

Creating this additional index for this version is not required.

Give it a try !!!

+17
source

better than a loop and 10 requests, you can ask for location.location_id a limit of 10 for pagination, combine this into a comma-separated string, and then a full request to get WHERE location.location_id IN (1,2,3...{list of ids})

+3
source

You can go with your original thought of grouping with location_id and then use the group_concat function to display all the addresses for that location as 1 field.

 SELECT l.location_id, l.location_name, t.type_id, t.type_name, group_concat(concat("Address: ",i.location_address, " Phone: ", i.location_phone)) as addresses FROM location AS l LEFT JOIN location_information AS i ON (l.location_id = i.location_id) LEFT JOIN location_types AS t ON (l.location_type_id = t.type_id) GROUP BY l.location_id ORDER BY l.location_id DESC LIMIT 10
+3
source

There are several ways to solve this problem:

  • You can add the IsPrimary bit IsPrimary to the location_information table and add a trigger so that each location always has only one location_information record with this set of 1.
  • You can select the oldest or newest location_information (MIN / MAX) entry using the location_id column if you do not have DateCreated or DateModified .
+2
source

More articles:


All Articles