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