0

In my MariaDB database I store combined metadata from multiple other sources (face databases). There is a 'main' table containing great majority of data, and each row is identified by both it's own ID, and additional Source_ID (foreign key) pointing to other table consisting of rows with unique face databases. Main table consists of nearly 17 million lines right now, and is expected to grow at least couple of times.

Naturally, SELECT * FROM 'main_table' where Source_ID = x, is my most used and most desired statement.

If I select data from a source that contains more than 10k lines, the length of a query grows as the count of data available from a specific source increases. For example, data from a source that contains 20k lines is usually returned in a couple of seconds, while 500k lines take 15-30 seconds to complete.

However, here is the phenomenon I cannot explain:

When I select a data from source that has 100 < X < 10000 lines, the query takes more than 2 (sometimes 5-7) minutes(!) to complete. During this query, server's RAM reaches maximum allocated count and processor has a noticeable load as well. When I perform a SELECT not by key value, but by Main Table ID instead (effectively selecting the very same data), query's duration decreases 5 - 10 times.

I have tried doing a fresh reinstall of MariaDB, with same issue persisting. I am using MariaDB 10.7, server has i7-2600, 16 GB RAM, running on Windows10. The issue happens whether I make SELECT query through HeidiSQL or through python code using mariadb module.

I would greatly appreciate any ideas on what could possibly be a cause of such issue, or, at least, how should I better think, test and probe this issue in order to discover the actual roots of it. Thank you in advance.

4
  • 1
    Demonstrate your structure as complete SHOW CREATE TABLE output (main and 1 dependent table). Show a sample of main table rows as INSERT INTO (2-3 rows).
    – Akina
    Commented Apr 21, 2022 at 7:24
  • Add the EXPLAIN ANALYZE to your post for both the fast and slow query. Likely you're getting different execution plans for each, perhaps where the slow one is scanning the whole Table (which explains why the RAM explodes), and the fast one is just seeking to the subset of rows needed.
    – J.D.
    Commented Apr 21, 2022 at 11:38
  • Let's see the schema.
    – Rick James
    Commented Apr 21, 2022 at 21:27
  • @J.D. Yes, your assumption seems to have been correct. Query does seem to scan and return ALL 17M rows from the table, and then - if i understand correctly - it applies the filter on these rows: "attached_condition": "main_data.databases_id = 107" However, question remains. What is it that could possibly cause some smaller queries perform under a different execution plan?
    – Wee
    Commented Apr 22, 2022 at 7:08

1 Answer 1

0

After reading @J.D 's response, I went in and performed several ANALYZE queries on the selects I previously did. It became quite apparent that for few specific queries specified by 'WHERE index = X', MariaDB wasn't using indexes, and instead returned the whole collection of lines, later filtering them by the 'WHERE' criteria.

What ended up solving this issue was the 'ANALYZE TABLE table_name' query, which, to my understanding, updates the collection of known indexes in databases records. This resulted all of my queries successfully use indexes from now on and perform queries in a reasonable amount of time.

I've found this post to be helpful, especially in case the index ignoring issue has deeper roots: mySQL query optimizer not using indexes

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.