[CDB] Topic Image Preview

A place for Extension Authors to post and receive feedback on Extensions still in development. No Extensions within this forum should be used within a live environment!
Anti-Spam Guide
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

IMPORTANT: Extensions Development rules

IMPORTANT FOR NEEDED EVENTS!!!
If you need an event for your extension please read this for the steps to follow to request the event(s)
User avatar
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5859
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman

Re: [3.2][ALPHA] Topic Image Preview

Post by MattF »

JoshyPHP wrote: Wed Feb 15, 2017 12:27 pm 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.
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
User avatar
JoshyPHP
Code Contributor
Posts: 1288
Joined: Mon Jul 11, 2011 12:28 am

Re: [3.2][ALPHA] Topic Image Preview

Post by 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.
I wrote the library that handles markup in phpBB 3.2+.
User avatar
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5859
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman

Re: [3.2][ALPHA] Topic Image Preview

Post by MattF »

JoshyPHP wrote: Wed Feb 15, 2017 8:53 pm 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.
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
User avatar
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5859
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman

Re: [3.2][ALPHA] Topic Image Preview

Post by MattF »

JoshyPHP wrote: Wed Feb 15, 2017 8:53 pm 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
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
User avatar
JoshyPHP
Code Contributor
Posts: 1288
Joined: Mon Jul 11, 2011 12:28 am

Re: [3.2][ALPHA] Topic Image Preview

Post by 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.
I wrote the library that handles markup in phpBB 3.2+.
User avatar
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5859
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman

Re: [3.2][ALPHA] Topic Image Preview

Post by MattF »

JoshyPHP wrote: Thu Feb 16, 2017 5:13 pm

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
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
User avatar
JoshyPHP
Code Contributor
Posts: 1288
Joined: Mon Jul 11, 2011 12:28 am

Re: [3.2][ALPHA] Topic Image Preview

Post by 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.
I wrote the library that handles markup in phpBB 3.2+.
User avatar
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5859
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman

Re: [3.2][ALPHA] Topic Image Preview

Post by MattF »

JoshyPHP wrote: Thu Feb 16, 2017 6:10 pm 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.
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
User avatar
JoshyPHP
Code Contributor
Posts: 1288
Joined: Mon Jul 11, 2011 12:28 am

Re: [3.2][ALPHA] Topic Image Preview

Post by 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.
I wrote the library that handles markup in phpBB 3.2+.
User avatar
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5859
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman

Re: [3.2][ALPHA] Topic Image Preview

Post by MattF »

The UNION ALL queries are the fastest.

Execution time: 0.01626897 seconds

Just need to get it happy with SQLite3 :?
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
User avatar
javiexin
Code Contributor
Posts: 1157
Joined: Wed Oct 12, 2011 11:46 pm
Location: Madrid, Spain
Name: Javier
Contact:

Re: [3.2][ALPHA] Topic Image Preview

Post by 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!
User avatar
MattF
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5859
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman

Re: [3.2][BETA] Topic Image Preview

Post by MattF »

Updated to version Beta 1
Formerly known as VSEMy ExtensionsPlease do not PM me for support.
User avatar
Galixte de EzCom
Registered User
Posts: 1106
Joined: Mon Oct 04, 2004 11:14 pm
Location: France
Name: Raphaël M.
Contact:

Re: [3.2][BETA] Topic Image Preview

Post by Galixte de EzCom »

Hi,

will the attachment files be supported by your extension?

FRENCH translation available on EzCom community.
Communau EzCom
📖 « Traductions d’extensions & styles pour phpBB 3.2.x & 3.3.x ».
📋 Lists of all extensions identified for phpBB 3.1.x & 3.2.x.
📋 Lists of all styles identified for phpBB 3.1.x & 3.2.x.
Image Tu as un forum et tu veux aussi un site web ? Regarde par ici.
Havym
Registered User
Posts: 62
Joined: Sun Jan 29, 2017 3:50 pm
Location: Netherlands
Name: Marco

Re: [3.2][BETA] Topic Image Preview

Post by 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
Registered User
Posts: 6
Joined: Sat Apr 21, 2018 11:34 am

Re: [3.2][BETA] Topic Image Preview

Post by 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 ?
Locked

Return to “Extensions in Development”