MySQL Indexes of Logs table

[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.

[iconnor]

Wouldn't remove them. Might add more.

[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.

[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.

[IgorA100]

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?

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?

if you ORDER BY on another field it won't use the very useful index.

Yes that's right.

[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.

[IgorA100]

I wrote about this:

09.jpg (461.56 KiB) Viewed 644 times

Or shouldn't it be like that?

[iconnor]

It should not exist.
1.37.58 will remove it and add a component index.