Accurate Pagination with left Joins

Question

I've been thinking about this for a while and its got to a point where I think its better to ask around and listen what other people think.

Im bulding a system that stores locations on Mysql. Every 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 10 most recently 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

Right? But the problem is that if a location has more than 1 address the limit/pagination is not going to be accurrate, unless I "GROUP BY l.location_id", but that is going to show only one address for each place.. what happens with the places that have multiple addresses?

So I thought the only way to solve this is by doing a query inside a loop.. Something like this (pseudocode):

$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 im getting the last 10 places, but by doing that I wind up with at least 10 queries more, and I dont think that helps the app performance.

Is there a better way to achieve what im looking for? (accurate pagination).

Answer 1

Here is your original query

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 perform the pagination last. If you refactor this query, you can perform the pagination 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) 
;

Notice I created a subquery called k. The 10 keys get picked up and ordered FIRST !!!

Then the JOINs can go on from there, hope using just 10 location_ids.

What will help the subquery k is an index that carries location_id and location_type_id

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

Here is something else you may like about this approach

How do you query for the next 10 ids (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 clause in subquery k with each new page.

• LIMIT 20,10
• LIMIT 30,10
• and so on...

I can improve the refactoring by removing the location table and have subquery k carry the needed fields like this:

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) 
;

Making that extra index would not be necessary for this version.

Give it a Try !!!