Hello

Because of the reported performance issues I have been tossing around ideas to solve the problem. One was to rely a few functions, that are follow ups of database operations, on database triggers, another one was to outsource tasks more or less completely to the database, that needs nowadays complex preparations in our PHP-code.

I begun with the tests independent from the corresponding issue #673. I thereby draw on my experience with MS SQL and its implementation of stored procedures (from now on SP). There I impemented a program with only SP, that decomposes the nested levels of a JSON object into its parts and key-value-pairs, analises them and selects the data depending of the curent dataset, to decide what of the dataset should be stored in another database.

As mentioned somewhere else for triggers in MySQL, I have – in a similar manner – no real experience with SP in MySQL. It's a different syntax as well as a smaller featureset than in MS SQL and, not to forget, I don't know anything about the performance in comparision with transmitting queries as strings from PHP (including their previous preparation in PHP). So I started from scratch with a small task. Getting the categories, a user has access to. Reminder: I do not expect to see a relevant change of the performance with my tests. This is only an experiment at the moment.

Currently getting the categories is a two stage task with the functions get_categories for selecting the accessible categories (if categories exist) and get_category_ids for extracting a list of their IDs from the previous generated category list. The first stage selects the categories depending of the function/rank of the requesting user (not logged in visitor, logged in user, moderator and administrator). The forum operator can regulate the access to categories depending of the rank (even this is not a full matching term). So the function checks the existence of categories and the rank of the requsting user to provide the proper list of categories (or nothing in case of not existing categories). Remind, that the general access to the forum is managed somewhere else.

Because of the limitations of MySQL I had to split similar tasks into different but similar (even not identival) looking SP.

Counting the categories for the decision to generate the selection (no categories means also no need to select them).

DROP PROCEDURE IF EXISTS get_CategoriesCount;
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS get_CategoriesCount(OUT cCats INT)
BEGIN
 SELECT COUNT(*) INTO cCats FROM mlf24_categories;
END //
DELIMITER ;

Select the list of accessible categories depending from the user rank with their ID and name

DROP PROCEDURE IF EXISTS get_AllowedCategories;
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS get_AllowedCategories(IN userID INT)
BEGIN
 DECLARE cntCategories INT DEFAULT 0;
 DECLARE userType SMALLINT DEFAULT -1;
 CALL get_CategoriesCount(cntCategories);
 
 IF cntCategories > 0 THEN
  IF userID IS NOT NULL AND userID > 0 THEN
   SELECT user_type INTO userType FROM mlf24_userdata WHERE user_id = userID;
   IF userType = 0 THEN
    SELECT id, category FROM mlf24_categories WHERE accession IN(0, 1) ORDER BY order_id ASC;
   ELSEIF userType IN(1, 2) THEN
    SELECT id, category FROM mlf24_categories WHERE accession IN(0, 1, 2) ORDER BY order_id ASC;
   ELSE
    SELECT id, category FROM mlf24_categories WHERE accession = 0 ORDER BY order_id ASC;
   END IF;
  ELSE
   SELECT id, category FROM mlf24_categories WHERE accession = 0 ORDER BY order_id ASC;
  END IF;
 END IF;
END //
DELIMITER ;

Select the list of accessible categories depending from the user rank with their ID only

DROP PROCEDURE IF EXISTS get_AllowedCategoryIDs;
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS get_AllowedCategoryIDs(IN userID INT)
BEGIN
 DECLARE cntCategories INT DEFAULT 0;
 DECLARE userType SMALLINT DEFAULT -1;
 CALL get_CategoriesCount(cntCategories);
 
 IF cntCategories > 0 THEN
  IF userID IS NOT NULL AND userID > 0 THEN
   SELECT user_type INTO userType FROM mlf24_userdata WHERE user_id = userID;
   IF userType = 0 THEN
    SELECT id FROM mlf24_categories WHERE accession IN(0, 1);
   ELSEIF userType IN(1, 2) THEN
    SELECT id FROM mlf24_categories WHERE accession IN(0, 1, 2);
   ELSE
    SELECT id FROM mlf24_categories WHERE accession = 0;
   END IF;
  ELSE
   SELECT id FROM mlf24_categories WHERE accession = 0;
  END IF;
 END IF;
END //
DELIMITER ;

After failing several times with generating the different PS because of syntax errors (MySQL is very strict with closing commands lines with semicola when it comes to SP) I moved on to a slightly more complex task. Counting the number of entries in accessible categories.

 
DROP PROCEDURE IF EXISTS get_EntriesCount;
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS get_EntriesCount(IN userID INT)
BEGIN
 DECLARE cntCategories INT DEFAULT 0;
 DECLARE userType SMALLINT DEFAULT -1;
 CALL get_CategoriesCount(cntCategories);
 
 IF cntCategories > 0 THEN
  IF userID IS NOT NULL AND userID > 0 THEN
   SELECT user_type INTO userType FROM mlf24_userdata WHERE user_id = userID;
   IF userType = 0 THEN
    SELECT
     COUNT(*)
    FROM mlf24_entries
     LEFT JOIN mlf24_akismet_rating ON mlf24_akismet_rating.eid = mlf24_entries.id
     LEFT JOIN mlf24_b8_rating ON mlf24_b8_rating.eid = mlf24_entries.id
    WHERE mlf24_entries.category IN(SELECT id FROM mlf24_categories WHERE accession IN(0, 1))
     AND (mlf24_akismet_rating.spam = 0 AND mlf24_b8_rating.spam = 0);
   ELSEIF userType IN(1, 2) THEN
    SELECT
     COUNT(*)
    FROM mlf24_entries
     LEFT JOIN mlf24_akismet_rating ON mlf24_akismet_rating.eid = mlf24_entries.id
     LEFT JOIN mlf24_b8_rating ON mlf24_b8_rating.eid = mlf24_entries.id
    WHERE mlf24_entries.category IN(SELECT id FROM mlf24_categories WHERE accession IN(0, 1, 2))
     AND (mlf24_akismet_rating.spam = 0 AND mlf24_b8_rating.spam = 0);
   ELSE
    SELECT
     COUNT(*)
    FROM mlf24_entries
     LEFT JOIN mlf24_akismet_rating ON mlf24_akismet_rating.eid = mlf24_entries.id
     LEFT JOIN mlf24_b8_rating ON mlf24_b8_rating.eid = mlf24_entries.id
    WHERE mlf24_entries.category IN(SELECT id FROM mlf24_categories WHERE accession = 0)
     AND (mlf24_akismet_rating.spam = 0 AND mlf24_b8_rating.spam = 0);
   END IF;
  ELSE
   SELECT
    COUNT(*)
   FROM mlf24_entries
    LEFT JOIN mlf24_akismet_rating ON mlf24_akismet_rating.eid = mlf24_entries.id
    LEFT JOIN mlf24_b8_rating ON mlf24_b8_rating.eid = mlf24_entries.id
   WHERE mlf24_entries.category IN(SELECT id FROM mlf24_categories WHERE accession = 0)
    AND (mlf24_akismet_rating.spam = 0 AND mlf24_b8_rating.spam = 0);
  END IF;
 ELSE
  SELECT
   COUNT(*)
  FROM mlf24_entries
   LEFT JOIN mlf24_akismet_rating ON mlf24_akismet_rating.eid = mlf24_entries.id
   LEFT JOIN mlf24_b8_rating ON mlf24_b8_rating.eid = mlf24_entries.id
  WHERE (mlf24_akismet_rating.spam = 0 AND mlf24_b8_rating.spam = 0);
 END IF;
END //
DELIMITER ;

I would expect the SP to be sligthly faster than the generation of the query strings with PHP. There would be only one much simpler query per task. The only data to transmit would be the user-id, everything else would be decided inside the SP on the database server. That way we could start with substituting complex constructions in PHP which prepares code for querying the database here and there with first SP which would move the complexity to the database server.

Until now I have not performed any test regarding running SP with PHP.

Pitfall here is, to being sure, that a common forum operator is allowed to create, alter and execute SP on her/his MySQL database instance when running a forum upgrade or installation. It would make absolutely no sense to begin with the implementation if forum operators would be unable to install or upgrade their forum instances because of strict limitations of their hosting ISPs. On the other hand we would be absolutely incapable to maintain a version, that (partially) runs with SP and another one, that runs the "classic" way.

With running SHOW GRANTS FOR 'database-user-name'; when logged in in phpMyAdmin I got the following result

Grants for database-user-name@%  
GRANT USAGE ON *.* TO `database-user-name`@`database-host`
GRANT ALL PRIVILEGES ON `database-name`.* TO `database-user-name`@`database-host`

With GRANT ALL PRIVILEGES my user is allowed to do more or less anything. This output could look completely different on other servers. The following permissions are explicitly required to handle SP: CREATE ROUTINE, ALTER ROUTINE and EXECUTE. It would be nice to see the anonymised output from other database servers. So please execute the query GRANT ALL PRIVILEGES on your server and post the anonymised output here in this thread.

Tschö, Auge