What's the best/fastest way to select all questions with a tag?

Question

In my tag synonyms plugin, when you edit tags it seems to take a very long time to retag questions. I am running a new query on every question to save it back to the database, which I don't think I can avoid.

But I also think I'm doing too much when selecting every tag initially. Here is the current code, which runs for each tag synonym to grab questions with the old tags:

$questions = qa_db_select_with_pending(
    qa_db_tag_recent_qs_selectspec( null, $syn['from'], 0, false, 500 )
);

The qa_db_select_with_pending function apparently selects a bunch of other stuff like options, which I don't need - not 10+ times anyway! Is there a better way to simply select all the questions with a particular tag?

 

Comments

BTW I'm probably gonna do the tag editing with AJAX like you do the user points recalc, but obviously I still don't want it to take forever!

Answer 1

what about something like:

qa_db_read_all_values(qa_db_query_sub(

    'SELECT postid FROM ^posts WHERE tags REGEXP \b$\b',

   $syn['from']

));

?

(I haven't tested this, btw).

Comments

You're right, it may be faster to use direct SQL. But I think the selectspec does a join on the words table to avoid searching by regex.

---

Did a quick test and using REGEXP is about 25 times slower - 0.19s versus 0.0072s for the join. I'll keep the selectspec I think!
Also FYI the syntax for word boundaries is: "[[:<:]]tag[[:>:]]"

Answer 2

You can use qa_db_single_select(...) instead of qa_db_select_with_pending(...) to just retrieve results for a single given selectspec, and this will ignore any pending selectspec retrievals.

BTW the qa_db_select_with_pending(...) function will only retrieve the extra stuff once, unless you're doing something unusual, so it shouldn't be a big worry.

Comments

Thanks. One more question - does using qa_post_set_content do reindexing of the tags once edited, or do I need to call a different function to keep qa_posttags updated?

---

It takes care of indexing, like all the functions in qa-app-posts.php.