Site usability testing results

All suggestions about TPFC should be posted here. Discussions about changes to TPFC will also be carried out here.
Message
Author
User avatar
SYSTEM
Posts: 1969
Joined: Sat Jul 31, 2010 1:19 am
Location: Helsinki, Finland

Re: Site usability testing results

#16 Post by SYSTEM » Sat Dec 26, 2020 2:59 am

vevy wrote:
Fri Dec 25, 2020 3:58 pm
Andrew Lee wrote:
Wed Dec 23, 2020 9:03 pm
The SQL query actually includes a lot of other stuff, including pagination, ordering, scoring etc. which are built up along various stages, cumulating into a single SQL query.
Isn't it better to leave those to the frontend?
No, doing pagination and scoring in the front end is slow since it requires sending too much data to the visitor. (Ordering wouldn't be, but pagination depends on order and thus ordering also needs to be done in the database.)
My YouTube channel | Release date of my 13th playlist: August 24, 2020

User avatar
vevy
Posts: 752
Joined: Tue Sep 10, 2019 11:17 am

Re: Site usability testing results

#17 Post by vevy » Sat Dec 26, 2020 4:30 pm

SYSTEM wrote:
Sat Dec 26, 2020 2:59 am
vevy wrote:
Fri Dec 25, 2020 3:58 pm
Isn't it better to leave those to the frontend?
No, doing pagination and scoring in the front end is slow since it requires sending too much data to the visitor. (Ordering wouldn't be, but pagination depends on order and thus ordering also needs to be done in the database.)
Of course, but I didn't mean it that way. Maybe I used the wrong term.
  • SQL query provides the list of apps matching the search term.
  • The part responsible for preparing the page itself (the PHP server?) handles what goes where; e.g. pagination, etc.
I meant to modularize and abstract the steps a bit to facilitate any needed change (like this one).
Help make the comprehensive CLI database happen:
                    Vote for filters/badges!

User avatar
Andrew Lee
Posts: 2603
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: Site usability testing results

#18 Post by Andrew Lee » Sat Dec 26, 2020 9:36 pm

In this particular case, I don't think doing it at the front end will buy much (unlike sorting a tabular list of values, for example).

The way it is currently done is to start with a generic select. Then it is slowly built along the pipeline to add constraints (search in title/desc/keywords, public/private etc.), ordering (date/popularity), and pagination (using offset/limit). So a single SQL returns the page of entries required (between 5 to 100, depending on user setting), which is passed to the templating engine to format into HTML, then pushed to the frontend. Very little processing occurs at the frontend.

The only bit that makes sense to do at the frontend is for a search listing, where all the entries are pushed to the frontend, and the frontend does the sorting, pagination and highlighting. Though technically possible, this requires making a massive exception for search results and requires a huge break away from the current architecture.

Doing it at the backend is far easier, since this entails only manually sorting and paginating the full search results (the size should already be greatly constrained by the query keywords). Still, this is quite a bit more work than the current hack, so I will just procrastinate for as long as I can :D

User avatar
vevy
Posts: 752
Joined: Tue Sep 10, 2019 11:17 am

Re: Site usability testing results

#19 Post by vevy » Sat Dec 26, 2020 9:41 pm

Andrew Lee wrote:
Sat Dec 26, 2020 9:36 pm
which is passed to the templating engine to format into HTML,
That part can't help?


Also, how about adding a virtual scoring column to the query where; e.g., a match in title adds 10 and a match in description adds 3, etc. Then use that column for sorting at the end of the query steps?
Still, this is quite a bit more work than the current hack, so I will just procrastinate for as long as I can :D
sigh! :P
Help make the comprehensive CLI database happen:
                    Vote for filters/badges!

User avatar
Andrew Lee
Posts: 2603
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: Site usability testing results

#20 Post by Andrew Lee » Sat Dec 26, 2020 9:57 pm

vevy wrote:
Sat Dec 26, 2020 9:41 pm
That part can't help?
How so? The templating engine is at the backend. The code for TPFC came before all the frontend madness in ReactJS and AngularJS that we have today (not that I know much about either of them :D).
vevy wrote:
Sat Dec 26, 2020 9:41 pm
Also, how about adding a virtual scoring column to the query where; e.g., a match in title adds 10 and a match in description adds 3, etc. Then use that column for sorting at the end of the query steps?
How can that be done?
Currently the SQL is in the form:

Code: Select all

select ... from ... where ... and
(
match(software.title) against ($keywords in boolean mode) or 
match(software.description) against ($keywords in boolean mode) or 
match(software.keywords) against ($keywords in boolean mode)
)
order by ...
limit $offset, $pagesize
The 3 matches may or may not exist depending on whether the user has opted to match against title/desc/keywords.

How do I create a virtual score for each row?

User avatar
vevy
Posts: 752
Joined: Tue Sep 10, 2019 11:17 am

Re: Site usability testing results

#21 Post by vevy » Mon Dec 28, 2020 10:38 pm

You can try something like:

Code: Select all

WITH scoretable (
    st_score,
    st_id,
    st_title,
    st_description,
    st_keywords,
    etc
)
AS (
    SELECT 10,
           id,
           title,
           description,
           keywords,
           etc
      FROM mytable
     WHERE title LIKE "%searchterm%"
    UNION ALL
    SELECT 3,
           id,
           title,
           description,
           keywords,
           etc
      FROM mytable
     WHERE description LIKE "%searchterm%"
    UNION ALL
    SELECT 5,
           id,
           title,
           description,
           keywords,
           etc
      FROM mytable
     WHERE keywords LIKE "%searchterm%"
)
SELECT SUM(st_score),
       st_id,
       st_title,
       st_description,
       st_keywords,
       etc
  FROM scoretable
 GROUP BY st_id
The number (10,3,5) is to give weight to certain fields.

Now you have with a score, where you can continue ordering etc.
Help make the comprehensive CLI database happen:
                    Vote for filters/badges!

User avatar
Andrew Lee
Posts: 2603
Joined: Sat Feb 04, 2006 9:19 am
Contact:

Re: Site usability testing results

#22 Post by Andrew Lee » Tue Dec 29, 2020 2:09 am

Thanks for the suggestion.

I understand your concept, but it won't be an easy fit either. The current "select" is already a huge, convoluted nightmare I am not sure I want to deal with 4x of that (with minor changes in each variation)!

But I will keep this idea in mind when I am next forced to refactor the code. :D

User avatar
vevy
Posts: 752
Joined: Tue Sep 10, 2019 11:17 am

Re: Site usability testing results

#23 Post by vevy » Tue Dec 29, 2020 7:15 am

👍

I am sure a scoring query can be optimized significantly, though.
Help make the comprehensive CLI database happen:
                    Vote for filters/badges!

Post Reply