Avatar

Some feedback regarding to database performance (General)

by Auge ⌂, Monday, April 03, 2023, 21:27 (331 days ago) @ Joe I

Hello

[checks for spam]

See therefore also my observations, which more or less confirm what you said, documented in the corresponding Github issue (#673).

6) From a SQL perspective, the biggest issue I see with these spam checks is there is basically no cardinality on these fields (0 or 1 only), plus MySQL does not have a great way of handling boolean fields.

MySQL has no boolean field type at all even there is one. This …

In MySQL, TinyInt(1) and Bit field types both use a full byte and do not have true boolean characteristics according to SQL standards.

… because the existing type boolean is a tinyint behind the hood (at least in MySQL 5.anything).

Since cardinality is so low, an index on these fields (even though they exist) is not of great value to the queries.

According to my linked observations the index in mlf2_b8_rating seems to work while the index in the similar structured table mlf2_akismet_rating does not work.

7) One note regarding the upgrade process. After upgrading, I looked at the SQL table indexes, and compared it to a clean install. After upgrade to 20220803.1, the mlf2_akismet_rating table was missing indexes on the spam and spam_check_status fields. These indexes exist on a clean install, but not an upgrade.

Thank you for spotting this.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Tags:
performance, database, MySQL


Complete thread:

 RSS Feed of thread