Page 2 of 2
Re: Site usability testing results
Posted: Sat Dec 26, 2020 2:59 am
by SYSTEM
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.)
Re: Site usability testing results
Posted: Sat Dec 26, 2020 4:30 pm
by vevy
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).
Re: Site usability testing results
Posted: Sat Dec 26, 2020 9:36 pm
by Andrew Lee
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
Re: Site usability testing results
Posted: Sat Dec 26, 2020 9:41 pm
by vevy
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
sigh! 
Re: Site usability testing results
Posted: Sat Dec 26, 2020 9:57 pm
by Andrew Lee
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
).
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?
Re: Site usability testing results
Posted: Mon Dec 28, 2020 10:38 pm
by vevy
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.
Re: Site usability testing results
Posted: Tue Dec 29, 2020 2:09 am
by Andrew Lee
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.
Re: Site usability testing results
Posted: Tue Dec 29, 2020 7:15 am
by vevy
I am sure a scoring query can be optimized significantly, though.
