[ACCEPTED]-Accurate Pagination with left Joins-performance

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 17 pagination last. If you refactor this query, you 16 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 15 I created a subquery called k. The 10 keys 14 get picked up and ordered FIRST !!!

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

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

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

Here is something else 9 you may like about this approach

How do you 8 query for the next 10 ids (ids 11 - 20) ? Like 7 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 6 in subquery k with each new page.

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

I can improve 5 the refactoring by removing the location 4 table and have subquery k carry the needed 3 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 2 would not be necessary for this version.

Give 1 it a Try !!!

better than looping and 10 queries, you 4 could query for the location.location_id 3 limit 10 for the pagination, concatenate 2 that into a comma separated string and then 1 the full query to get WHERE location.location_id IN (1,2,3...{list of ids})

You could go with your original thought 4 on grouping by the location_id and then 3 use the group_concat function to display 2 all of the addresses for that location as 1 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

There are a few ways to solve this:

• You could add an IsPrimary bit column to the location_information table, and add a trigger to ensure each location always has only one location_information record with this set to 1.
• You could select the oldest or newest location_information record (MIN/MAX), using the location_id column if you have no DateCreated or DateModified columns.

0