[3.2][BETA] 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!
Scam Warning
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
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 4484
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [3.2][ALPHA] Topic Image Preview

Post by VSE » Wed Feb 15, 2017 8:26 pm

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.
Official phpBB Extensions My Extensions & MODs
Please do not PM me for support.
Dictated but not read.

User avatar
JoshyPHP
Code Contributor
Posts: 756
Joined: Mon Jul 11, 2011 12:28 am

Re: [3.2][ALPHA] Topic Image Preview

Post by JoshyPHP » 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.
I wrote the thing that does the BBCodes in 3.2. Unless it broke yours, in which case it was somebody else with a similar name.

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 4484
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [3.2][ALPHA] Topic Image Preview

Post by VSE » Wed Feb 15, 2017 9:12 pm

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.
Official phpBB Extensions My Extensions & MODs
Please do not PM me for support.
Dictated but not read.

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 4484
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [3.2][ALPHA] Topic Image Preview

Post by VSE » Thu Feb 16, 2017 4:48 pm

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
Official phpBB Extensions My Extensions & MODs
Please do not PM me for support.
Dictated but not read.

User avatar
JoshyPHP
Code Contributor
Posts: 756
Joined: Mon Jul 11, 2011 12:28 am

Re: [3.2][ALPHA] Topic Image Preview

Post by JoshyPHP » Thu Feb 16, 2017 5:13 pm

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 thing that does the BBCodes in 3.2. Unless it broke yours, in which case it was somebody else with a similar name.

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 4484
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [3.2][ALPHA] Topic Image Preview

Post by VSE » Thu Feb 16, 2017 6:03 pm

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
Official phpBB Extensions My Extensions & MODs
Please do not PM me for support.
Dictated but not read.

User avatar
JoshyPHP
Code Contributor
Posts: 756
Joined: Mon Jul 11, 2011 12:28 am

Re: [3.2][ALPHA] Topic Image Preview

Post by JoshyPHP » 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.
I wrote the thing that does the BBCodes in 3.2. Unless it broke yours, in which case it was somebody else with a similar name.

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 4484
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [3.2][ALPHA] Topic Image Preview

Post by VSE » Thu Feb 16, 2017 6:26 pm

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.
Official phpBB Extensions My Extensions & MODs
Please do not PM me for support.
Dictated but not read.

User avatar
JoshyPHP
Code Contributor
Posts: 756
Joined: Mon Jul 11, 2011 12:28 am

Re: [3.2][ALPHA] Topic Image Preview

Post by JoshyPHP » Thu Feb 16, 2017 6:46 pm

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 thing that does the BBCodes in 3.2. Unless it broke yours, in which case it was somebody else with a similar name.

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 4484
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [3.2][ALPHA] Topic Image Preview

Post by VSE » Thu Feb 16, 2017 8:17 pm

The UNION ALL queries are the fastest.

Execution time: 0.01626897 seconds

Just need to get it happy with SQLite3 :?
Official phpBB Extensions My Extensions & MODs
Please do not PM me for support.
Dictated but not read.

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 » Thu Feb 16, 2017 9:08 pm

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
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 4484
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [3.2][BETA] Topic Image Preview

Post by VSE » Fri Apr 21, 2017 6:28 pm

Updated to version Beta 1
Official phpBB Extensions My Extensions & MODs
Please do not PM me for support.
Dictated but not read.

User avatar
Galixte de EzCom
Registered User
Posts: 828
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 » Wed Sep 20, 2017 5:29 am

Hi,

will the attachment files be supported by your extension?

FRENCH translation available on EzCom community.
Communau EzCom
Image « Traductions d’extensions & styles pour phpBB 3.1.x & 3.2.x ».
ImageLists of all extensions identified for phpBB 3.1.x & 3.2.x.
ImageLists of all styles identified for phpBB 3.1.x & 3.2.x.

Post Reply

Return to “Extensions in Development”

Who is online

Users browsing this forum: karbiko, planetemuscle and 27 guests

cron