[CDB] Topic Image Preview

[MattF]

Oh ok, I understand now, both the purpose and why the query will end up being slow if topics get a bit too big. (assuming this isn't an issue with the query plan)

If you resume work on that extension you may want to try replacing the subquery with a derived table that you would join to the posts table using post_id. MySQL generally handles derived tables better.

Thanks for the tip. I came up with a new query using derived tables that now results in 17,000 posts being queried in milliseconds!

So I posted a new ALpha 3 version.

Joshy Maybe you could look at my query, and see if it can be improved? It works now, but because I am using Group By, postgresql is failing to work with it now. Otherwise, it's good for MySQL and SQLite.

[JoshyPHP]

PostgreSQL will like it better if you push the GROUP BY to the derived table:

Code: Select all

$func = ($this->config->offsetGet('vse_tip_new')) ? 'MAX' : 'MIN';
$sql = 'SELECT p.topic_id, p.post_text
	FROM (
		SELECT ' . $func . '(post_id) AS post_id
		FROM ' . POSTS_TABLE . '
		WHERE ' . $this->db->sql_in_set('topic_id', $topic_list) . '
			AND post_text ' . $this->db->sql_like_expression('<r>' . $this->db->get_any_char() . '<IMG ' . $this->db->get_any_char()) . '
		GROUP BY topic_id)
	AS d
	JOIN ' . POSTS_TABLE . ' p USING (post_id)';

I haven't tested it but I would expect it to perform about as well as your current query. I'd be interested in seeing the query plan if you can post it, out of curiosity.

[MattF]

PostgreSQL will like it better if you push the GROUP BY to the derived table:

Code: Select all

$func = ($this->config->offsetGet('vse_tip_new')) ? 'MAX' : 'MIN';
$sql = 'SELECT p.topic_id, p.post_text
	FROM (
		SELECT ' . $func . '(post_id) AS post_id
		FROM ' . POSTS_TABLE . '
		WHERE ' . $this->db->sql_in_set('topic_id', $topic_list) . '
			AND post_text ' . $this->db->sql_like_expression('<r>' . $this->db->get_any_char() . '<IMG ' . $this->db->get_any_char()) . '
		GROUP BY topic_id)
	AS d
	JOIN ' . POSTS_TABLE . ' p USING (post_id)';

I haven't tested it but I would expect it to perform about as well as your current query. I'd be interested in seeing the query plan if you can post it, out of curiosity.

Wow! Thanks JoshyPHP!! That worked a lot better!

Here's the plan I was able to get from my SQL App:

Screen Shot 2017-02-15 at 1.08.54 PM.png

That's for a big run on a large DB. Query time was about 630ms. My query was in the high 500s so, close enough. Not sure how to get a visual plan though.

[MattF]

I haven't tested it but I would expect it to perform about as well as your current query. I'd be interested in seeing the query plan if you can post it, out of curiosity.

Here is a visual plan I was able to get

Screen Shot 2017-02-16 at 8.46.21 AM.png

[JoshyPHP]

I'm surprised it doesn't use the topic_id index for the derived table. Try running ANALYZE TABLE, see if it changes. Ideally you'd find a way to use the tid_post_time index and the server would bail as soon as the first row is found. That could be done with a dependent subquery in the SELECT clause like so:

Code: Select all

SELECT p.topic_id, p.post_text
  FROM (
    SELECT (
              SELECT p.post_id
                FROM phpbb_posts p
               WHERE p.topic_id = t.topic_id
                 AND p.post_text LIKE '%<IMG %'
            ORDER BY p.post_time
               LIMIT 1
           ) AS post_id
      FROM phpbb_topics t
     WHERE t.topic_id IN (1, 2, 3)
  ) AS d
  JOIN phpbb_posts p USING (post_id)

Or a simple UNION query:

Code: Select all

(SELECT topic_id, post_text FROM phpbb_posts WHERE topic_id = 1 ORDER BY post_time LIMIT 1)
UNION ALL
(SELECT topic_id, post_text FROM phpbb_posts WHERE topic_id = 2 ORDER BY post_time LIMIT 1)
UNION ALL
(SELECT topic_id, post_text FROM phpbb_posts WHERE topic_id = 3 ORDER BY post_time LIMIT 1)

I don't know how portable this is. There's a possibility that doing one query per topic would be close in terms of performance if the database is hosted on the same machine as the web server.

[MattF]

Code: Select all

SELECT p.topic_id, p.post_text
  FROM (
    SELECT (
              SELECT p.post_id
                FROM phpbb_posts p
               WHERE p.topic_id = t.topic_id
                 AND p.post_text LIKE '%<IMG %'
            ORDER BY p.post_time
               LIMIT 1
           ) AS post_id
      FROM phpbb_topics t
     WHERE t.topic_id IN (1, 2, 3)
  ) AS d
  JOIN phpbb_posts p USING (post_id)

This query seems to be a LOT better.

The previous query was running an execution time of about 1.1~1.2 seconds.

Screen Shot 2017-02-16 at 10.01.56 AM.png

Screen Shot 2017-02-16 at 10.01.46 AM.png

Screen Shot 2017-02-16 at 10.02.03 AM.png

This new one you just posted is much faster, about 0.2~0.3 seconds.

Screen Shot 2017-02-16 at 9.59.14 AM.png

Screen Shot 2017-02-16 at 9.59.20 AM.png

Screen Shot 2017-02-16 at 10.00.15 AM.png

[JoshyPHP]

It still examines way more rows than I'd expect: 43363 rows examined for 18 rows returned. Do those topics have 2,000 posts on average?

You should look into the UNION query and perhaps even use one query per topic for the DBMSes that don't like LIMIT in UNION queries.

[MattF]

It still examines way more rows than I'd expect: 43363 rows examined for 18 rows returned. Do those topics have 2,000 posts on average?

You should look into the UNION query and perhaps even use one query per topic for the DBMSes that don't like LIMIT in UNION queries.

Yeah some of those topics have thousands of posts. On the page being checked here, among its topics displayed there are 17,118 posts. Only seems like SQLite is failing on this new query.

I actually want to avoid running 25 separate queries (1 per topic) since each one contains a LIKE clause. 0.3 seconds for a forum this big seems pretty decent to me. I will look into the union query too though.

[JoshyPHP]

The WHERE clause doesn't really matter. If anything, the downside of executing 25 queries is the network overhead.

You may have read the LIKE queries are bad, or seen such queries perform badly. The issue is with queries where the only predicate is a LIKE comparison. Without any other predicate, the database has to do a full table scan to read every row. As it is, that LIKE clause is applied to tens of thousands of rows already, in order to filter them before they are sorted. If you can get the database server to examine rows in order (by exploiting the tid_post_time index) it'll examine a fraction of that number and it won't even sort any rows. If you try the UNION query in your query visualizer and you shouldn't see any full table scan or any filesort.

[MattF]

The UNION ALL queries are the fastest.

Execution time: 0.01626897 seconds

Just need to get it happy with SQLite3

[javiexin]

Hey guys,

Thanks a lot for such a great example of collaborative development!

Thanks a lot to both, and congratulations! I, for one, have learnt a lot!

[MattF]

Updated to version Beta 1

[Galixte de EzCom]

Hi,

will the attachment files be supported by your extension?

FRENCH translation available on EzCom community.

[Havym]

Wow, very nice extension. Thank you.

Is it possible to make a summary of images in a topic like suggested in this request?

[yemliha24]

Hi. Thank you for extensions.

İ make extarnal page slider.

forum url /forum
page url /home.php

how i show lates topic image slider ?

Can you help me please ?