Optimising MySQL queries – JOIN vs IN (subquery)

When searching for some advice relating to an SQL query I was writing recently, I read some advice that you should try to use JOINs rather than IN with a subquery. I modified the particular query I was working on to use a JOIN instead of a subquery, and lo and behold, it was much faster.

So I decided to try and rewrite some other queries I’d written using IN with a subquery. Re-written to use JOINs instead, they should be much faster. But rather than just blindly rewriting the queries, I decided to run a few tests.

I should probably note here that it is certainly possible that I messed up in writing the tests, or in calculating the results. Just like everyone else, I sometimes make mistakes. If anyone thinks I have made a mistake somewhere, please leave a comment to let me know what I did wrong. You can see the test and the results below.

What I found is that actually whether a JOIN or IN subquery is faster depends on how much of the data the query selects. For queries that only returned one row, a JOIN was a lot faster. For queries that returned a lot of rows, a subquery was faster. It seems to relate to how many rows exist in the table being queried, and how many records your subquery returns. This is partly due to how MySQL evaluates queries – the outer query is evaluated first, and then the subquery.

To give some number examples, in my test using IN subquery took around 0.0264470117s on average when the subquery returned only one row, but took 0.013940505s (about half the time) when the subquery matched 1215 rows. (The table results were returned from held 3791 records in total). Using JOIN took around 0.0007992231s when there was only one matching row, extremely fast compared to using IN subquery. But when the query matched 1215 rows, it was quite a bit slower than using a subquery, taking 0.0369988716s on average. These were all with LIMIT 0,30.

On a more complicated query, using a subquery took 0.0057s when matching 1215 rows, and 0.0307s for 1 row. Using JOINs instead took 1.1343s when matching 1215 rows, and 0.0041 for 1 row. Over 1s vs 0.0057s is a massive difference in favour of using subqueries.

INNER JOIN vs LEFT JOIN where no rows will be NULL

Another thing I learned recently was that when you are JOINing with a table where you are using a JOIN ON or WHERE clause that means no rows will be NULL, then you should (or at least might as well) use INNER JOIN rather than LEFT JOIN. I tested this for efficiency, and it doesn’t seem to make any difference.

DISTINCT vs GROUP BY

Finally, I decided to test DISTINCT against GROUP BY, both methods that can be used to get results with no duplicate records. Now, according to most of the people responding to this question: What’s faster, SELECT DISTINCT or GROUP BY in MySQL? they are both equivalent, or DISTINCT may be faster depending on whether you have an INDEX on the column being sorted.

Notice, however, that apart from one poster, all the replies are based on theory rather than actual test results. The one person who does supply a test result shows GROUP BY to be faster.

In my tests, I found GROUP BY was always faster than DISTINCT, though by such as small amount as to not matter for most queries. When using a JOIN rather than a subquery though, GROUP BY was about twice as fast as using DISTINCT.

Conclusion

For the particular queries that these tests were based on, I intend on keeping them as subqueries. Generally the pages viewed most will be those where a lot of records are matched. Although it is slower when matching fewer records, the speed is still perfectly fine. The slowest query when using a subquery was faster than the slowest query when using a JOIN.

I will change my queries to use INNER JOIN where applicable, just because it fits in with the logic of the query rather than any benefit. I will also continue to use GROUP BY rather than DISTINCT.

Ultimately, the takeaway is that you shouldn’t just trust information you read. You should try it out with your own queries and see whether it does provide any benefit or not. And remember to test with different parameters to see how well it copes with selecting lots or only a few records.

Testing Methodology

When testing different ways of doing things, I like to use a loop to repeat the same actions multiple times. This lets you get a better picture of the average speed. I also like to call the tests in a random order. This way, if there is something that makes the last test run slower, it will be equalled out across all tests, as they all have an equal chance of being run last. It also means that things like how much CPU time your computer is using for doing other stuff should affect all tests equally.

Below is the test that I ran. globalFuncs.inc.php is a file that sets up the database connection and assigns it to $conn. This test won’t be any good for you to run on your own server, since you don’t have my database. But it should give you an idea of what I tested, and how my testing methodology works.

<?php
include('globalFuncs.inc.php');

$ids=array('16'=>16,'22'=>22);
$groups=array('distinct' => array('distinct' => 'DISTINCT', 'group by' => ''),
              'group by' => array('distinct' => '', 'group by' => 'GROUP BY images.id')
);
$orders=array('images.id' => 'images.id ASC', 'images.Rating, images.id' => 'images.Rating ASC, images.id DESC');
$limits=array('none'=>'', '0,30'=>'LIMIT 0,30');
$queries = array(
    'subquery' => 'SELECT %s images.id, images.Headline, images.filename, images.Rating, images.url_name
        FROM images
        WHERE images.id IN (SELECT img_categories.img_id
            FROM img_categories
            LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
            categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND parent.id = %d)
        %s
        ORDER BY %s
        %s',
    'subquery as table' => 'SELECT %s images.id, images.Headline, images.filename, images.Rating, images.url_name
        FROM images
        WHERE images.id IN (SELECT img_id FROM (
            SELECT img_categories.img_id
            FROM img_categories
            LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
            categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND parent.id = %d) AS x )
        %s
        ORDER BY %s
        %s',
    'JOIN' => 'SELECT %s images.id, images.Headline, images.filename, images.Rating, images.url_name
        FROM images
        LEFT JOIN img_categories ON img_categories.img_id = images.id
        LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
        categories AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.id = %d
        %s
        ORDER BY %s
        %s',
    'subquery EXISTS' => 'SELECT %s images.id, images.Headline, images.filename, images.Rating, images.url_name
        FROM images
        WHERE EXISTS (SELECT 1
            FROM img_categories
            LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
            categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND parent.id = %d
            AND img_categories.img_id=images.id)
        %s
        ORDER BY %s
        %s',
    'subquery inner' => 'SELECT %s images.id, images.Headline, images.filename, images.Rating, images.url_name
        FROM images
        WHERE images.id IN (SELECT img_categories.img_id
            FROM img_categories
            INNER JOIN categories AS node ON node.id = img_categories.categories_id,
            categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND parent.id = %d)
        %s
        ORDER BY %s
        %s',
    'subquery as table inner' => 'SELECT %s images.id, images.Headline, images.filename, images.Rating, images.url_name
        FROM images
        WHERE images.id IN (SELECT img_id FROM (
            SELECT img_categories.img_id
            FROM img_categories
            INNER JOIN categories AS node ON node.id = img_categories.categories_id,
            categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND parent.id = %d) AS x )
        %s
        ORDER BY %s
        %s',
    'JOIN inner' => 'SELECT %s images.id, images.Headline, images.filename, images.Rating, images.url_name
        FROM images
        INNER JOIN img_categories ON img_categories.img_id = images.id
        INNER JOIN categories AS node ON node.id = img_categories.categories_id,
        categories AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.id = %d
        %s
        ORDER BY %s
        %s',
    'subquery EXISTS inner' => 'SELECT %s images.id, images.Headline, images.filename, images.Rating, images.url_name
        FROM images
        WHERE EXISTS (SELECT 1
            FROM img_categories
            INNER JOIN categories AS node ON node.id = img_categories.categories_id,
            categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND parent.id = %d
            AND img_categories.img_id=images.id)
        %s
        ORDER BY %s
        %s'
);
//open a CSV file for writing results to
$results = fopen('./results.csv', 'w+');
$resultsArray=array();
//write column headings
fwrite($results, '"id","group","order","limit","query","time","num rows"');
//test the query combinations
for($i=0;$i<10000;$i++){
    set_time_limit(20);
    //array_rand returns a random key from the array, not a random value
    $id = array_rand($ids);
    $group = array_rand($groups);
    $order = array_rand($orders);
    $limit = array_rand($limits);
    $query = array_rand($queries);
    //prepare the sql statement
    $sql = sprintf($queries[$query], $groups[$group]['distinct'], $ids[$id], $groups[$group]['group by'], $orders[$order], $limits[$limit]);
    //echo "<p>$sql</p>";
    //run the query
    $start = microtime(true);
    $result = $conn->query($sql);
    $end = microtime(true);
    $num_rows = $result->num_rows;
    $result->close();
    if($conn->errno){
        throw new Exception($sql."\n".$conn->error);
    }
    $time = $end-$start;
    //write the combination of factors used and the time taken to the results spreadsheet
    fwrite($results, "\n".$id.',"'.$group.'","'.$order.'","'.$limit.'","'.$query.'",'.$time.','.$num_rows);
    //add the time to the resultsArray
    if(!isset($resultsArray[$sql])){
        $resultsArray[$sql]=array();
    }
    $resultsArray[$sql][]=$time;
}
//Print the summary results to the page
foreach($resultsArray as $sql => $times){
    $num=count($times);
    echo "<p>$sql<br />#Number of runs: $num Avg time taken: ".(array_sum($times)/$num).'</p>';
}

The above generated the following output, which lists every different query tested, along with the number of tests of that query and the average speed:

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 71 Avg time taken: 0.027030031446

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 76 Avg time taken: 0.0015269329673366

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 97 Avg time taken: 0.035095794913695

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 82 Avg time taken: 0.0047898786823924

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 84 Avg time taken: 0.44326896894546

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 84 Avg time taken: 0.0048105716705322

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 93 Avg time taken: 0.049238925339073

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 75 Avg time taken: 0.00077086448669434

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 79 Avg time taken: 0.40961661519884

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 ORDER BY images.id ASC
#Number of runs: 87 Avg time taken: 0.00089040569875432

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 71 Avg time taken: 0.056139596751038

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 74 Avg time taken: 0.027898350277463

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 80 Avg time taken: 0.026139560341835

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 65 Avg time taken: 0.0014580689943754

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 72 Avg time taken: 0.035247064299054

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 102 Avg time taken: 0.0050803329430374

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 86 Avg time taken: 0.02539852330851

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 75 Avg time taken: 0.00063296953837077

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 73 Avg time taken: 0.0013659947539029

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) ORDER BY images.id ASC
#Number of runs: 69 Avg time taken: 0.0046912759974383

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 88 Avg time taken: 0.0044105594808405

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) ORDER BY images.id ASC
#Number of runs: 87 Avg time taken: 0.029684318893257

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 79 Avg time taken: 0.42213000828707

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 62 Avg time taken: 0.02683319968562

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 86 Avg time taken: 0.0045461017032002

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 84 Avg time taken: 0.00089811994915917

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 65 Avg time taken: 0.026387192652776

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 ORDER BY images.id ASC
#Number of runs: 68 Avg time taken: 0.055421857272877

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 72 Avg time taken: 0.02637243270874

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 79 Avg time taken: 0.024936416481115

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) ORDER BY images.id ASC
#Number of runs: 79 Avg time taken: 0.028783282147178

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 74 Avg time taken: 0.0016876008059527

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 83 Avg time taken: 0.0043915122388357

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 79 Avg time taken: 0.028693033170096

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 76 Avg time taken: 0.0052450769825986

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) ORDER BY images.id ASC
#Number of runs: 82 Avg time taken: 0.028383557389422

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 86 Avg time taken: 0.023148938666943

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 89 Avg time taken: 0.033621321903186

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 78 Avg time taken: 0.0046595824070466

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 87 Avg time taken: 0.0047145876391181

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) ORDER BY images.id ASC
#Number of runs: 68 Avg time taken: 0.026608049869537

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 62 Avg time taken: 0.023714846180331

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 81 Avg time taken: 0.050450969625402

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 83 Avg time taken: 0.026606622948704

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 78 Avg time taken: 0.025187290631808

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 74 Avg time taken: 0.016099420753685

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 81 Avg time taken: 0.029215485961349

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 89 Avg time taken: 0.027281552218319

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 75 Avg time taken: 0.030121971766154

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 78 Avg time taken: 0.02580447991689

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 78 Avg time taken: 0.42831136018802

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 81 Avg time taken: 0.048666306483893

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 56 Avg time taken: 0.0010733136108943

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 79 Avg time taken: 0.0046716792674004

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 64 Avg time taken: 0.014305341988802

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 80 Avg time taken: 0.00077477991580963

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 78 Avg time taken: 0.028870056837033

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 73 Avg time taken: 0.027316070582769

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) ORDER BY images.id ASC
#Number of runs: 92 Avg time taken: 0.033111170582149

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 74 Avg time taken: 0.027759600330043

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 78 Avg time taken: 0.0011579501323211

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 72 Avg time taken: 0.41496338115798

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 70 Avg time taken: 0.00081805842263358

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 79 Avg time taken: 0.015369164792797

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 72 Avg time taken: 0.025377608007855

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 ORDER BY images.id ASC
#Number of runs: 78 Avg time taken: 0.00071749626061855

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 86 Avg time taken: 0.036545503971189

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 79 Avg time taken: 0.00070793115639988

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 87 Avg time taken: 0.026168609487599

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 85 Avg time taken: 0.025975751876831

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 89 Avg time taken: 0.0053393063920268

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) ORDER BY images.id ASC
#Number of runs: 84 Avg time taken: 0.027086649622236

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 91 Avg time taken: 0.0045050788711716

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 83 Avg time taken: 0.0013258715710008

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 75 Avg time taken: 0.025719575881958

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 77 Avg time taken: 0.026352990757335

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 69 Avg time taken: 0.0054904654406119

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 66 Avg time taken: 0.03561354044712

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 76 Avg time taken: 0.43758514366652

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 85 Avg time taken: 0.052388306225047

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 69 Avg time taken: 0.024982967238495

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) ORDER BY images.id ASC
#Number of runs: 76 Avg time taken: 0.031586035301811

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 64 Avg time taken: 0.00073864683508873

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 80 Avg time taken: 0.027619826793671

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 81 Avg time taken: 0.026247616167422

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 87 Avg time taken: 0.00074013622327783

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 81 Avg time taken: 0.025297968475907

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 62 Avg time taken: 0.026290932009297

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 78 Avg time taken: 0.00076335821396265

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) ORDER BY images.id ASC
#Number of runs: 80 Avg time taken: 0.0047037571668625

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 82 Avg time taken: 0.025243337561445

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 75 Avg time taken: 0.050343017578125

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 73 Avg time taken: 0.027872043113186

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 72 Avg time taken: 0.036670158306758

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 79 Avg time taken: 0.0013094853751267

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 86 Avg time taken: 0.025530224622682

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 78 Avg time taken: 0.42923699586819

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) ORDER BY images.id ASC
#Number of runs: 84 Avg time taken: 0.032432817277454

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 82 Avg time taken: 0.026294812923524

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 84 Avg time taken: 0.032917215710595

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images LEFT JOIN img_categories ON img_categories.img_id = images.id LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 86 Avg time taken: 0.00080746273661769

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 67 Avg time taken: 0.00064024284704408

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 84 Avg time taken: 0.0012844460351127

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 77 Avg time taken: 0.030228333039717

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 75 Avg time taken: 0.028929748535156

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 80 Avg time taken: 0.025855201482773

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 89 Avg time taken: 0.027789244491063

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 78 Avg time taken: 0.033232908982497

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) AS x ) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 102 Avg time taken: 0.0048423701641606

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 79 Avg time taken: 0.023344033881079

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 ORDER BY images.id ASC
#Number of runs: 95 Avg time taken: 0.054862642288208

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 76 Avg time taken: 0.022783317063984

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 87 Avg time taken: 0.40549411170784

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) ORDER BY images.id ASC
#Number of runs: 78 Avg time taken: 0.43066768462841

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 75 Avg time taken: 0.027738517125448

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 69 Avg time taken: 0.015849393347035

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) GROUP BY images.id ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 78 Avg time taken: 0.025098656996703

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 73 Avg time taken: 0.025534773526126

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 64 Avg time taken: 0.0012097284197807

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) ORDER BY images.id ASC
#Number of runs: 71 Avg time taken: 0.41745501840618

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images INNER JOIN img_categories ON img_categories.img_id = images.id INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 ORDER BY images.id ASC LIMIT 0,30
#Number of runs: 77 Avg time taken: 0.00088839097456499

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 83 Avg time taken: 0.025455156004572

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22) GROUP BY images.id ORDER BY images.id ASC
#Number of runs: 69 Avg time taken: 0.028578557829926

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC LIMIT 0,30
#Number of runs: 68 Avg time taken: 0.3998475951307

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_id FROM ( SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) AS x ) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 72 Avg time taken: 0.42009277145068

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 22 AND img_categories.img_id=images.id) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 67 Avg time taken: 0.02555652162922

SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE images.id IN (SELECT img_categories.img_id FROM img_categories LEFT JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16) GROUP BY images.id ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 60 Avg time taken: 0.033886698881785

SELECT DISTINCT images.id, images.Headline, images.filename, images.Rating, images.url_name FROM images WHERE EXISTS (SELECT 1 FROM img_categories INNER JOIN categories AS node ON node.id = img_categories.categories_id, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 16 AND img_categories.img_id=images.id) ORDER BY images.Rating ASC, images.id DESC
#Number of runs: 85 Avg time taken: 0.032469530666576

You can download the results spreadsheet, which also has some calculations I did to summarize the results here: results.ods.gz (Gzipped openoffice spreadsheet). The summary results are below. Avg refers to the average time the query took, Num refers to the number of times that type of query was run. (The higher the number, the more reliable the average figure).

INNER JOIN vs LEFT JOIN
Type Avg Num
subquery 0.0258382885 1256
subquery inner 0.0250546769 1227
subquery as table 0.1610412084 1276
subquery as table inner 0.152149437 1277
JOIN 0.020252807 1225
JOIN inner 0.0205932834 1255
subquery EXISTS 0.0251283091 1208
subquery EXISTS inner 0.0247927192 1276
Lots of records (id 16) vs small amount (id 22) w/ LIMIT 0,30 & LEFT JOIN / INNER aggregated
Type id Avg Num
subquery 22 0.0264470117 643
subquery 16 0.013940505 634
subquery as table 22 0.0047066287 692
subquery as table 16 0.2215603 590
JOIN 22 0.0007992231 590
JOIN 16 0.0369988716 633
subquery EXISTS 22 0.0260955988 644
subquery EXISTS 16 0.0144021435 593
GROUP BY vs DISTINCT w/ LEFT JOIN / INNER aggregated
Type Group Avg Num
subquery group by 0.0253810094 1200
subquery distinct 0.0255165765 1283
subquery as table group by 0.1561119418 1310
subquery as table distinct 0.1571011819 1243
JOIN group by 0.0136067026 1202
JOIN distinct 0.0268380302 1278
subquery EXISTS group by 0.024847748 1245
subquery EXISTS distinct 0.0250646173 1239
ORDER w/ type=JOIN / JOIN inner & LIMIT 0,30
Group Order Avg Num
group by images.id 0.0122829478 278
distinct images.id 0.0278775495 315
group by images.Rating, images.id 0.0122543925 315
distinct images.Rating, images.id 0.02487494 315
Posted on by xoogu, last updated

Leave a Reply