I am currently building a custom project which collects large amounts of data, and I am now in a process of processing that data. One particular table, which holds all of the data that's been collected, has about 412,000 rows. That isn't a big table in terms of records, but the size of the table stands at 120GB, and in the last week I've noticed the processor that trawls this data has stopped working. I'd have noticed it sooner but I'm also dealing with a 4 month old baby that hates sleep!

Anyway, there has been no change to the code, and when I run the code it hangs. Going further in to the processor code, the query where it loads the data is the issue. I had intended for it to do 100 queries every 2 minutes, but even limiting this to 1 record doesn't return anything. Putting the same query in to dbeaver results in nothing. The query is running as far as dbeaver is concerned, but it doesn't return anything.

I thought indexes may be an issue, as there are two columns that form part of the query that have no indexes on them. These columns are just flags indicating that the record has been processed, and that it may have encountered an error. So the fields are either 0 or 1. But when I add the index, everything hangs and nothing is done. Last night before I had to give my baby his bottle, I attempted to add the indexes using dbeaver, and when I got down this morning it was still doing it nearly 12 hours later.

In terms of the query, the query runs like this:

FROM source_data sd
WHERE sd.processed = '0'
AND sd.source_term LIKE '%searchTerm%'
AND sd.process_error = '0'

The source_term column has a BTree index with a cardinality of 412,000.

The server on which the database is held is sat under my desk, and here is the graph of it's performance over the last day.

enter image description here

The drop in the RAM usage is when I power cycled the server last night, and the spike in CPU usage is a mix of the server coming back online as well as me attempting to run the processor.

I am left in a pickle right now, as this processor is crucial to make sense of the data it contains and right now this particular table is locked. Other tables aren't affected. I've another table on this database with 50,000,000 records and while a query takes a few seconds to run it will still provide the results. I've also never been in a position where I've had a situation like this with a table being unresponsive. So I would appreciate any help in rescuing this table!

Thank you.

Edit: The table structure is this:

id - bigint(20)
source_term - varchar(500)
collated_data - longtext
created - int(11)
processed - tinyint(1)
processed_error - tinyint(1)

The source_term is a string and varies in it's structure, hence why I need to do a wild card search on that particular field.

  • 412,000 rows .. 120GB ?? 300 kb per row? what is this table structure? show it... If this is a table with extreme amount of unused space then perform OPTIMIZE TABLE. AND sd.source_term LIKE '%searchTerm%' This is non-SARGable condition which causes full table scan, without exclusions. Of course fullscan over 120Gb of data takes too must time... The source_term column has a BTree index with a cardinality of 412,000. Makes no sense - this is the whole value cadinality, not a substring presence cardinality. And it cannot be used for this condition.
    – Akina
    Apr 12, 2022 at 9:05
  • @Akina I've updated the question to show the table structure. Apr 12, 2022 at 9:13
  • I'd recommend you to divide your table and move collated_data column into separate linked 1:1 table. I do not see another way for improvement. Except some special case when searchTerm is a value from definite values list which can be formalized. Also you may think about FTS, but its applicability depends on possible searchTerm values.
    – Akina
    Apr 12, 2022 at 9:27
  • 1
    The index doesn't help if your predicate is sd.source_term LIKE '%searchTerm%'. The use of a wildcard at the front of that pattern means it will not use the index. You might like to read my presentation: Full Text Search Throwdown which tests different fulltext solutions for MySQL. Apr 12, 2022 at 17:28
  • It seems "unresponsive" but it would almost certainly finish eventually, if you waited long enough for it. But it could still take longer than you can wait. Or perhaps longer than your lifespan! Apr 12, 2022 at 17:31


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.