MySQL – Does Join Occur Before LIMIT?

database-designMySQL

Imagine that I want to populate the last 10 questions of stackoverflow. I have a table that keep the questions list, a table to keep the question info, a table to list question's asker.

Now imagine that I want to get the last ten questions, so I have to join some tables and get the last items by ORDER BY ... DESC LIMIT 0 , 10. This gets me the last ten questions without any problems, but I think that there may be one issue.

MySQL may be first joining all tables and then pick the last ten items, so if I have to join 1 million rows to 1 million rows, it gets really bad performance.

I want to know if MySQL does do this, and if yes, how can I go around this issue?.

Best Answer

The only way around it is to refactor the query so as to alter the order of the EXPLAIN plan.

EXAMPLE : On May 16, 2011, I once answered a very rough question on StackOverflow

Fetching a Single Row from Join Table

Question : I had to join tables together and return only the 40 most recent results.
My Answer : I proved that you can do a JOIN on tables with large rows and get results fast.
How I did it : I had applied principles of refactoring queries I learned from a Youtube Video.

Basically, you have to construct the query so that JOIN clauses are done last. Consequently, this implies that you must get the LIMIT executed beforehand.

SUGGESTION

Perhaps you should start with a GROUP BY ... HAVING query like this

SELECT table_name,COUNT(1) table_count
FROM information_schema.tables
WHERE table_schema NOT IN
('information_schema','performance_schema','mysql')
GROUP BY table_name HAVING COUNT(1) > 1;

This will definitely give all tables whose name appears in multiple databases

Form that as a subquery and join it to gather all databases the table appears in

SELECT
    A.table_name,
    GROUP_CONCAT(B.table_schema) TheTableAppearsInTheseDatabases
FROM
(
    SELECT table_name,COUNT(1) table_count
    FROM information_schema.tables
    WHERE table_schema NOT IN
    ('information_schema','performance_schema','mysql')
    GROUP BY table_name HAVING COUNT(1) > 1
) A INNER JOIN information_schema.tables B
USING (table_name) GROUP BY A.table_name;

Please notice that I use INNER JOIN rather than LEFT JOIN because it will really form a Cartesian product (2704 X 2704) and then perform comparisons.

I know this works because I tried it out in MySQL 5.5.12 on my Windows7 machine

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>     SELECT
    ->         A.table_name,
    ->         GROUP_CONCAT(B.table_schema) TheTableAppearsInTheseDatabases
    ->     FROM
    ->     (
    ->         SELECT table_name,COUNT(1) table_count
    ->         FROM information_schema.tables
    ->         WHERE table_schema NOT IN
    ->         ('information_schema','performance_schema','mysql')
    ->         GROUP BY table_name HAVING COUNT(1) > 1
    ->     ) A INNER JOIN information_schema.tables B
    ->     USING (table_name) GROUP BY A.table_name;
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
| table_name               | TheTableAppearsInTheseDatabases                                                                                   |
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
| a                        | junk,test,robottinosino                                                                                           |
| acl                      | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| blocks                   | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| blog                     | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| blog_category            | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| blog_entrycat            | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| blog_meta                | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_archive,weisci_jaws_staging2                                     |
| blog_trackback           | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                     |
| calendar_events          | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| calendar_meta            | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                     |
| calendar_questions       | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| calendar_tickets         | weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                     |
| calendar_transactions    | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging,weisci_jaws_archive                                     |
| captcha_complex          | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| change_log               | test,junk                                                                                                         |
| chat_staff               | test,junk                                                                                                         |
| comments                 | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging,weisci_jaws_archive                                     |
| donations_charities      | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| donations_charities_meta | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| donations_donations      | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| foo_reference1           | timpost1,timpost2                                                                                                 |
| foo_reference2           | timpost1,timpost2                                                                                                 |
| foo_reference3           | timpost1,timpost2                                                                                                 |
| groups                   | weisci_jaws_staging,weisci_jaws_archive,weisci_jaws_live,weisci_jaws_staging2                                     |
| ipvisitor                | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| job_post                 | giannosfor,test                                                                                                   |
| layout                   | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| listeners                | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| mediamanager_files       | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_staging                                                         |
| mediamanager_group       | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| mediamanager_photos      | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| mediamanager_video       | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| menus                    | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| menus_groups             | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| mytable                  | ryanzec,user1267617,cabita,dotancohen,johnlocke,neeraj,test,user391986,cool_cs,javier,mathieu                     |
| mytext                   | jakobud,newstuff                                                                                                  |
| occupation_field         | giannosfor,test                                                                                                   |
| policy_agentblock        | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| policy_ipblock           | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| prova                    | veto,vito                                                                                                         |
| registry                 | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| registry_bk              | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                                         |
| session                  | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| static_pages             | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| static_pages_translation | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| t                        | preeti,rollup_test                                                                                                |
| t1                       | abidibo,test                                                                                                      |
| t2                       | test,abidibo                                                                                                      |
| t3                       | test,abidibo                                                                                                      |
| table1                   | table_test,supercoolville,test                                                                                    |
| table2                   | supercoolville,test,table_test                                                                                    |
| tags                     | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tags_content             | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tbl_banner_position      | weisci_jaws_staging2,weisci_jaws_live                                                                             |
| tbl_banner_upload        | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                                         |
| tbl_global_banner        | weisci_jaws_staging2,weisci_jaws_live                                                                             |
| tms_authors              | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tms_repositories         | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| tms_themes               | weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging                                     |
| updates                  | test,junk                                                                                                         |
| url_aliases              | weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2,weisci_jaws_archive                                     |
| url_maps                 | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| users                    | weisci_jaws_archive,weisci_jaws_staging,giannosfor,veto,weisci_jaws_live,weisci_jaws_staging2,friends,sample,vito |
| users_groups             | weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live,weisci_jaws_staging2                                     |
| users_meta               | weisci_jaws_staging2,weisci_jaws_archive,weisci_jaws_staging,weisci_jaws_live                                     |
+--------------------------+-------------------------------------------------------------------------------------------------------------------+
65 rows in set (0.91 sec)

mysql>

Give it a Try !!!

MySQL – Pagination Without OFFSET

Here's one way:

mysql> #select all in reverse
mysql> select id, createdAt from post order by createdAt desc, id desc;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   16 | 2014-11-16 09:11:18 |
|   15 | 2014-11-16 09:11:03 |
|   14 | 2014-11-16 09:10:32 |
|   13 | 2014-11-16 09:10:05 |
|   12 | 2014-11-16 09:09:47 |
|   11 | 2014-11-13 18:35:01 |
|   10 | 2014-11-13 18:33:08 |
|    9 | 2014-11-13 18:33:01 |
|    8 | 2014-11-13 17:51:21 |
|    7 | 2014-11-13 17:40:30 |
|    6 | 2014-11-12 18:37:44 |
|    5 | 2014-11-12 18:21:44 |
|    4 | 2014-11-12 18:17:30 |
|    3 | 2014-11-12 17:31:31 |
|    2 | 2014-11-11 18:37:40 |
|    1 | 2014-11-11 17:28:41 |
+------+---------------------+
16 rows in set (0.01 sec)

mysql> #select last two
mysql> select id, createdAt from post order by createdAt desc, id desc limit 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   16 | 2014-11-16 09:11:18 |
|   15 | 2014-11-16 09:11:03 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #select next button
mysql> SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   14 | 2014-11-16 09:10:32 |
|   13 | 2014-11-16 09:10:05 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #select next button again
mysql> SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:10:05' AND (id < '13' OR createdAt < '2014-11-16 09:10:05') ORDER BY createdAt DESC, id DESC LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   12 | 2014-11-16 09:09:47 |
|   11 | 2014-11-13 18:35:01 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #undo next button (just to get back conceptually)
mysql> SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   14 | 2014-11-16 09:10:32 |
|   13 | 2014-11-16 09:10:05 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #we already saw 'next' from here
mysql> #now let's click the previous button
mysql> #change the direction of the inequalities and order by statement
mysql> SELECT id, createdAt FROM post  WHERE createdAt >= '2014-11-16 09:10:32' AND (id > 14 OR createdAt > '2014-11-16 09:10:32') ORDER BY createdAt asc, id asc LIMIT 2;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   15 | 2014-11-16 09:11:03 |
|   16 | 2014-11-16 09:11:18 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> #and if you want them sorted in desc:
mysql> select id, createdAt from (SELECT id, createdAt FROM post  WHERE createdAt >= '2014-11-16 09:10:32' AND (id > 14 OR createdAt > '2014-11-16 09:10:32') ORDER BY createdAt asc, id asc LIMIT 2 ) a order by createdAt desc, id desc;
+------+---------------------+
| id   | createdAt           |
+------+---------------------+
|   16 | 2014-11-16 09:11:18 |
|   15 | 2014-11-16 09:11:03 |
+------+---------------------+
2 rows in set (0.00 sec)

Best Answer

Fetching a Single Row from Join Table

Related Solutions

Mysql – Should I use left join to do the job in this scenario

SUGGESTION

MySQL – Pagination Without OFFSET

Related Question