Page 1 of 1
MySQL Indexes of Logs table
Posted: Mon Mar 25, 2024 5:05 pm
by IgorA100
For Level there are two indexes Logs_TimeKey_idx and Logs_Level_idx . Perhaps one of them needs to be removed?
There are no indexes for Code and Component. In this regard, MySQL thinks for a very long time when selecting by Code and Component at the same time.
Re: MySQL Indexes of Logs table
Posted: Tue Mar 26, 2024 10:54 pm
by iconnor
Wouldn't remove them. Might add more.
Re: MySQL Indexes of Logs table
Posted: Sat Apr 06, 2024 9:35 am
by IgorA100
Without indexes everything is bad.
With more than 2 million records in the "Logs" table (DB on HDD) we have:
Code: Select all
# Query_time: 12.691928 Lock_time: 0.000009 Rows_sent: 0 Rows_examined: 1031635
SET timestamp=1712395334;
SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs` WHERE (TimeKey LIKE '%not send%' OR Component LIKE '%not send%' OR ServerId LIKE '%not send%' OR Pid LIKE '%not send%' OR Code LIKE '%not send%' OR Message LIKE '%not send%' OR File LIKE '%not send%' OR Line LIKE '%not send%') ORDER BY TimeKey DESC LIMIT 0, 200;
Code: Select all
# Query_time: 6.909203 Lock_time: 0.000008 Rows_sent: 1 Rows_examined: 1031615
SET timestamp=1712395327;
SELECT count(*) AS Total FROM Logs WHERE (TimeKey LIKE '%not send%' OR Component LIKE '%not send%' OR ServerId LIKE '%not send%' OR Pid LIKE '%not send%' OR Code LIKE '%not send%' OR Message LIKE '%not send%' OR File LIKE '%not send%' OR Line LIKE '%not send%');
It's a very, very long time.
Re: MySQL Indexes of Logs table
Posted: Sun Apr 07, 2024 2:40 pm
by iconnor
It does not makes sense to search logs without specifying a time range, hence the TimeKey index is very useful. I don't understand why you are asking to remove indexes and also complaining about the speed of searching through millions of records. Language barrier I assume.
I agree that an index on the component and code could be useful, but you can't index every column and it slows the db down on inserts.
Also mysql is stupid. I recently discovered that if you ORDER BY on another field it won't use the very useful index. We default to sorting by TimeKey so if you don't specify a timeKey it won't use the other indexes.
Check out EXPLAIN on the sql querying the logs table. The results may surprise you.
Re: MySQL Indexes of Logs table
Posted: Sun Apr 07, 2024 4:04 pm
by IgorA100
iconnor wrote: ↑Sun Apr 07, 2024 2:40 pm
I don't understand why you are asking to remove indexes
I made a mistake in my first message. There are two indexes "TimeKey" and "Logs_TimeKey_idx", they both index the column "TimeKey", why?
iconnor wrote: ↑Sun Apr 07, 2024 2:40 pm
I agree that an index on the component and code could be useful, but you can't index every column and it slows the db down on inserts.
Agree. Have you tested how much the insertion will slow down if you add a couple more indexes?
iconnor wrote: ↑Sun Apr 07, 2024 2:40 pm
if you ORDER BY on another field it won't use the very useful index.
Yes that's right.
Re: MySQL Indexes of Logs table
Posted: Sun Apr 07, 2024 9:32 pm
by iconnor
THe only indexes should be as follows:
CREATE INDEX `Logs_TimeKey_idx` ON `Logs` (`TimeKey`);
CREATE INDEX `Logs_Level_idx` ON `Logs` (`Level`);
We can add more, but as I was saying mysql won't use them unless we also change the ORDER BY. What we probably need to do is REMOVE the sorting and let bootstrap-table do the sorting which I think it may do anyways... but then the pagination gets weird.
Re: MySQL Indexes of Logs table
Posted: Sun Apr 07, 2024 9:48 pm
by IgorA100
I wrote about this:
- 09.jpg (461.56 KiB) Viewed 1331 times
Or shouldn't it be like that?
Re: MySQL Indexes of Logs table
Posted: Mon Apr 08, 2024 2:01 pm
by iconnor
It should not exist.
1.37.58 will remove it and add a component index.