phpmyadmin Question posted in
The official documentation can be found here.

Improve sql code performance for a mariaDB query – Phpmyadmin

I developed an application connected to mariaDB (Ver 15.1 Distrib 10.1.31-MariaDB, for Win32) in DelphiXE8.
I want to improve query performance.
Describe the simplified scenario:

de_User Table (innoDB) (rows 81762)

ID_U   INT PRIMARY KEY
Name   VARCHAR(30)
INDEX ID_U,  Name

de_doc Table (innoDB) (rows 260452)

IDD   INT PRIMARY KEY
DataFi  Date
UserID  INT
...
INDEX IDD, UserID, DataFi
----
CONSTRAINT UserID_LK
FOREIGN KEY de_Doc  (UserID)
REFERENCES  de_User (ID_U)
ON DELETE CASCADE
ON UPDATE CASCADE

my query

select User.*, Doc.LastDoc
FROM de_Users AS Us 
LEFT JOIN (
SELECT UserID,MAX(DataFi) AS LastDoc
FROM de_doc 
GROUP BY UserID
) as Doc on Doc.UserID = Us.ID_U

ORDER BY Us.Name ASC, Doc.LastDoc DESC;

—
EXPLAIN select …

+------+-------------+----------------+-------+---------------+---------------+---------+----------------+--------+---------------------------------+
| id   | select_type | table          | type  | possible_keys | key      | key_len | ref            | rows   | Extra                           |
+------+-------------+----------------+-------+---------------+---------------+---------+----------------+--------+---------------------------------+
|    1 | PRIMARY     | de_User        | ALL   | NULL          | NULL     | NULL    | NULL           |  81762 | Using temporary; Using filesort |
|    1 | PRIMARY     | <derived2>     | ref   | key0          | key0     | 5       | Base.Us.ID_U   |     10 |                                 |
|    2 | DERIVED     | de_Doc         | index | NULL          | UserID_LK| 4       | NULL           | 260452 |                                 |
+------+-------------+----------------+-------+---------------+---------------+---------+----------------+--------+---------------------------------+

my.ini
…

# The MySQL server
[mysqld]
...
key_buffer = 4096M
key_buffer_size=1024M
table_open_cache = 2048
query_cache_size = 128M
max_connections = 100
...
max_allowed_packet = 256M
sort_buffer_size = 4096M
net_buffer_length = 16M
read_buffer_size = 256M
myisam_sort_buffer_size = 256M
log_error = "mysql_error.log"
...
# Comment the following if you are using InnoDB tables
innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data"
innodb_log_arch_dir = "C:/xampp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2048M
# DEPRECATED innodb_additional_mem_pool_size = 1024M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
...
thread_concurrency = 4
...
[isamchk]
key_buffer = 1024M
sort_buffer_size = 256M
read_buffer = 8M
write_buffer = 16M

[myisamchk]
key_buffer = 1024M
sort_buffer_size = 256M
read_buffer = 8M
write_buffer = 8M

TEST phpmyadmin:

83705 total, the query employed 1,0000 sec.
if I remove "order by Doc.LastDoc DESC" it is very fast
83705 total, the query employed 0,0000 sec.

TEST in my application developed with delphiEX8

view table all rows 2,8 sec.
if I remove "order by Doc.LastDoc DESC" it is very fast
view table all rows 1,8 sec.

How can I improve performance?

Tags:

5

Answers


  1. Chosen as BEST ANSWER
    0

    Changing these values in my.ini, in phpmyadmin here is the improved result.

    The time it takes to populate the grid in my Delphi application, now 1.9 sec compared to before 2.8 sec.

    my pc has 8Gb RAM;

    Can I reduce the time to populate the grid in Delphi? Maybe I have to make a new request for this.

    innodb_buffer_pool_size = 2048M
# Set .._log_file_size to 25 % of buffer pool size

    BEFORE

    innodb_log_file_size = 64M

    (83705 del total, The query employed 1,0000 sec.)

    AFTER

    innodb_log_file_size = 512M

    (83705 del total, The query employed 0,0000 sec.)


  2. 0

    Try this query and check whether the output is same as your query

    select Us.*,  max(Doc.DataFi) as LastDoc
FROM de_Users AS Us 
LEFT JOIN de_doc as Doc on Doc.UserID = Us.ID_U
group by Us.ID_U   
ORDER BY Us.Name ASC, LastDoc DESC;
    Login or Signup to reply.
  3. 0
    • This is ambiguous: INDEX IDD, UserID, DataFi
    • Probably User.* was supposed to be Us.*? Be aware that “simplifying” a query may turn it into a different problem.
    • Probably LEFT JOIN is unnecessary; use JOIN.
    • You need this composite INDEX(UserID, LastDoc)
    • Do you really want 82K rows in the output? What will the client do with that much data? I ask because if the client will further digest the results, maybe such would be better done in SQL.
    • When timing, be sure to avoid the Query cache by using SELECT SQL_NO_CACHE.
    • phpmyadmin probably tacks on a LIMIT, thereby changing what the Optimizer will do!
    • ORDER BY t1.a, t2.b (different tables) makes it impossible to use an index for ordering. This will prevent any sort of short-circuiting of the query.
    Login or Signup to reply.
  4. 0

    Suggestions for your my.ini [mysqld] SECTION

    sort_buffer_size=2M  # from 4096M (4G) of RAM per connection, next 2 are per connect also
read_buffer_size=256K  # from 256M to reduce volume of data retrieved by 99%
read_rnd_buffer_size=256K  # from ? to a reasonable size

    These three could be dynamically set (as root) with SET GLOBAL variable_name=value replace K with *1024 and M with *1024*1024 for Kbytes and Megabytes, please. Please post positive/negative results after a full BUSINESS DAY of uptime.

    Login or Signup to reply.
  5. 0

    If your goal is “grouwise-max”, then you left out a clause:

    select  User.*, Doc.LastDoc
    FROM  de_Users AS Us
    LEFT JOIN  
    (
        SELECT  UserID,MAX(DataFi) AS LastDoc
            FROM  de_doc
            GROUP BY  UserID 
    ) as Doc  ON Doc.UserID = Us.ID_U
             AND Doc.LastDoc = Us.DataFi    -- this was missing
    ORDER BY  Us.Name ASC, Doc.LastDoc DESC;

    That will also lead to many fewer rows being delivered, hence addressing the performance question.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search