Not sure if I got this right but in one case you do SELECT COUNT(*) FROM transactions; which is fast because it bypasses IMHU (in my humble understanding) the table scan and uses table metadata to get number of rows which is fast. and in the other you do SELECT COUNT(recipient) FROM transactions; If you want to count recipients and not transactions you need to use COUNT(DISTINCT recipient) and add an INDEX to recipient to be fastish otherwise you go a full table scan as the EXPLAIN demonstrates. Hope that helps. Vassilis On 3/24/25 6:15 PM, Olivier Miquel via discuss wrote:
Hello,
Thanks for your answer.
In this case the slowdown is occuring with simple select queries on a table. No join.
How could this be possible between two versions?
O.
Le 24/03/2025 à 16:34, Gordan Bobic a écrit :
If you are seeing a difference of 3.5 orders of magnitude, that is almost certainly not something you can tune out with configuration options. You'll need to look at the queries and work out which ones are now running with a sub-optimal execution profile. Once you identify them, you'll have to apply index or join order hints to get them back onto a sensible execution plan.
On Mon, 24 Mar 2025 at 17:16, Olivier Miquel via discuss <discuss@lists.mariadb.org> wrote:
Hello,
I am experiencing major performance problems when upgrading our version of mariadb from debian 10 ( mariadb 10.3 ) to debian 12 ( mariadb 10.5 ). It's especially when accessing a table with about 2 million records that requests are much slower ( 4.7 seconds vs 0.001 s ) to the point of making our application unusable. I've observed this between two versions of mariadb, each with default settings in Debian.
I've tried changing the settings several times, but nothing seems to help.
Do you have any idea why this big difference in performance and how to fix it?
Thanks for your help,
______________
_______
MariaDB [cfms_accounting]> DESCRIBE transactions; +---------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | reference | varchar(255) | NO | UNI | NULL | | | recipient | varchar(255) | YES | | NULL | | | sender | varchar(255) | YES | | NULL | | | sender_reference | varchar(255) | YES | | NULL | | | amount | double(15,8) | NO | | NULL | | | amount_type | varchar(255) | NO | | NULL | | | old_balance | double(15,8) | NO | | NULL | | | new_balance | double(15,8) | NO | | NULL | | | remark | text | YES | | NULL | | | order | varchar(255) | YES | | NULL | | | form_state | text | YES | | NULL | | | creator_user_id | int(10) unsigned | NO | MUL | NULL | | | accounting_group_id | int(10) unsigned | NO | MUL | NULL | | | working_group_id | int(10) unsigned | YES | MUL | NULL | | | resource_id | int(10) unsigned | YES | MUL | NULL | | | transaction_type_id | int(10) unsigned | NO | MUL | NULL | | | start_time | timestamp | NO | | NULL | | | end_time | timestamp | NO | MUL | NULL | | | created_at | timestamp | NO | | NULL | | | updated_at | timestamp | NO | | NULL | | | deleted_at | timestamp | YES | MUL | NULL | | +---------------------+------------------+------+-----+---------+----------------+
MariaDB [cfms_accounting]> ANALYZE TABLE transactions; +------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------------+---------+----------+----------+ | cfms_accounting.transactions | analyze | status | OK | +------------------------------+---------+----------+----------+ 1 row in set (0,107 sec)
Server version: 10.3.39-MariaDB-0+deb10u2 Debian 10
MariaDB [cfms_accounting]> SELECT count(*) from transactions; +----------+ | count(*) | +----------+ | 1912563 | +----------+ 1 row in set (0,616 sec)
MariaDB [cfms_accounting]> EXPLAIN select count(recipient) from transactions; +------+-------------+--------------+------+---------------+------+---------+------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------+------+---------------+------+---------+------+---------+-------+ | 1 | SIMPLE | transactions | ALL | NULL | NULL | NULL | NULL | 1942730 | | +------+-------------+--------------+------+---------------+------+---------+------+---------+-------+ 1 row in set (0,002 sec)
MariaDB [cfms_accounting]> select count(recipient) from transactions; +------------------+ | count(recipient) | +------------------+ | 419 | +------------------+ 1 row in set (0,001 sec)
Server version: 10.11.11-MariaDB-0+deb12u1 Debian 12
MariaDB [cfms_accounting]> ANALYZE TABLE transactions; +------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------------+---------+----------+----------+ | cfms_accounting.transactions | analyze | status | OK | +------------------------------+---------+----------+----------+ 1 row in set (0,058 sec)
MariaDB [cfms_accounting]> select count(*) from transactions; +----------+ | count(*) | +----------+ | 1912563 | +----------+ 1 row in set (0,333 sec)
MariaDB [cfms_accounting]> EXPLAIN select count(recipient) from transactions; +------+-------------+--------------+------+---------------+------+---------+------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------+------+---------------+------+---------+------+---------+-------+ | 1 | SIMPLE | transactions | ALL | NULL | NULL | NULL | NULL | 1779448 | | +------+-------------+--------------+------+---------------+------+---------+------+---------+-------+ 1 row in set (0,002 sec)
MariaDB [cfms_accounting]> select count(recipient) from transactions; +------------------+ | count(recipient) | +------------------+ | 419 | +------------------+ 1 row in set (4,778 sec)
-- Olivier Miquel Administrateur Système et Réseau Plate-forme technologique MRI Tel : 06.50.19.27.43 Mail:olivier.miquel@mri.cnrs.fr
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org