0

TLDR: I have a table with a lot of records and my queries are slow. I have indexes, but still the query takes a long time to execute. Need help figuring out what the problem is, or atleast a pointer in the right direction.

Long story below:-

I have a table of products with roughly 2.6M records and the size is set to grow. The table is de normalized and has around 100 odd columns to handle attributes of products objects. Products are categorized with a category name. Each category can have around 5-20 attribute columns. I am using MariaDB 11.0.2 running on Ubuntu on a 4 CPU core linode instance with 8GB Memory

Each record stores its own attributes in respective columns and while querying my code picks the right columns for the attributes that are being sorted or filtered. I kept the table de-normalized to avoid large joins and to speed up sorting and filtering. I need to filter and sort and filter based on a lot of these columns, sometimes at the same time.

I tried indexes with single key and multiple keys and was able to get speed improvements in certain cases. When multiple columns are being used, multicolumn indexes are used, but when columns of different indexes are used, the indexes are not used a full table scan is done. I tried to force an index but still the query took around 20 seconds.

For example when trying to count number of products meeting two where conditions take around 9 seconds to execute

There is a limit of 16 keys per index and a keys size of 3075 bytes (IIRC). If I try to filter or sort across keys from multiple indexes, the operation resorts to a full table scan and the whole operation takes around 20s to complete. Can the mentioned limits be increased using configuration entries? There are no frequently accessed columns to create a multicolumn index just on those columns. Almost all columns are equally possible.

For more context, the table hosts electronic products each with about 20 or more attributes. I know Mysql is capable of handling 100M records, please point me in the right direction.

The schema of the table is given below.

MariaDB [productsdb]> desc products;

https://pastebin.com/GAedK5kq

I have the following indexes on the table as well. The indexes starting with name 'abcd' was created for testing and will not be used in production.

MariaDB [productsdb]> show indexes from products;

https://pastebin.com/TLHuShMZ

I tried to issue a select query from products table using multiple columns in where clause.

MariaDB [productsdb]> SELECT count(`products`.`id`) FROM `products` WHERE (products.main_category_id = 439 and ( attr_3 in ('400mW','250mW') ) and ( attr_9 in ('7') ));
+------------------------+
| count(`products`.`id`) |
+------------------------+
|                    190 |
+------------------------+
1 row in set (8.949 sec)

With regards to the above query I have indices on main_category_id, attr_3, attr_9 and id columns. I do not have a multi column index comprising of these columns. It is faster for this specific query if I have a multi column index containing all these columns.

But I have so many attributes, how will I be able to do all permutation combination?

4
  • welcome. Previous comments where for anyone answering.
    – danblack
    Jul 24, 2023 at 5:59
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Jul 24, 2023 at 7:24
  • So couple of things: 1. Please provide the EXPLAIN ANALYZE of the example slow query you provided, as that'll help point you in the direction of the bottleneck. 2. Despite your indexes, the IN clause in your query could be causing an inefficient plan to be used to serve the query, but we won't know til we see the EXPLAIN ANALYZE, 3. You may not need to index the additional attr_# columns if there's a good enough index on more common columns such as main_category_id. Is this typically used to filter with in queries?...if so, an index on (main_category_id, id) may be best here.
    – J.D.
    Jul 24, 2023 at 12:30
  • It seems clear to me -- It's a variant of EAV, and hence performance of random queries sucks.
    – Rick James
    Jul 25, 2023 at 2:21

3 Answers 3

1

I am answering my question here. This may not be the best solution, but It works for me for the time being. Thanks to the inputs from everyone here.

The products table contained 2.6M records with a ton of attributes which were equally likely to be filtered upon and the select queries when filters were applied was very slow.

As per the ideas from the comments to the question, I created a separate table which housed the product_id and another filed named combined_attrs. I populated the table with the data from my main table. I could have done the same with an additional column in the products table itself, but I did not want to mess with the indexes on the production table.

I took care to make sure that all attribute values pertaining to a product is stored together in the combined_attrs column in a safe to search format. The attribute values may have spaces or special characters in them. I removed them using regexp_replace and prefixed all attribute values with the attribute name / ID. This helped me to make sure I avoid false positives when filtering for a specific attribute with a value.

I created a fulltext index on the combined_attrs field on the new table.

Next I changed my filtering framework and split the original filter query on products table into two queries. First to create filter queries against the new table and retrieve product IDs matching the filter query. Next to query the products table with category and other conditions along with the products.id in (filtered_product_ids).

Though I'm firing two queries overall, the speed is much better.

Original

MariaDB > SELECT COUNT(`products`.`id`) FROM `products` WHERE (products.main_category_id = 439 and ( attr_4 in ('±25ppm') ) and ( attr_5 in ('±15%') ) and ( attr_10 in ('10') ));
+------------------------+
| COUNT(`products`.`id`) |
+------------------------+
|                      6 |
+------------------------+
1 row in set (23.053 sec)

The original query took 23s to query table

New method

MariaDB > SELECT `product_filter_caches`.`product_id` FROM `product_filter_caches` WHERE (match(combined_attrs) against('+attr_4_±25ppm, +attr_5_±15%, +attr_10_10' in boolean mode) );
+------------+
| product_id |
+------------+
|    1964336 |
|    1964369 |
|    1964372 |
|    1964373 |
|    1964390 |
|    1964391 |
|    1964392 |
|    1964393 |
|    1964394 |
+------------+
9 rows in set (0.475 sec)

MariaDB > SELECT count(products.id) FROM `products` WHERE products.main_category_id = 439 and products.id in (1964336, 1964369, 1964372, 1964373, 1964390, 1964391, 1964392, 1964393, 1964394);
+--------------------+
| count(products.id) |
+--------------------+
|                  9 |
+--------------------+
1 row in set (0.002 sec)

The new method runs both queries with a total of under 500ms

To keep the things nice and clean, whenever a products is created / updated / removed from the admin panel, the filter table is also updated with the latest attribute values.

1
  • You are even allowed to tick it after 24hrs. Jul 28, 2023 at 10:31
0

"Almost all columns are equally possible."

This Answer aims to speed up a table scan, which you seem doomed to execute.

  • Never use TINYTEXT; it is functionally similar to VARCHAR(255), but has performance and indexing properties that bad.

  • Shrink all the datatypes where possible, such as...

  • INT takes 4 bytes; pick a smaller datatype unless you really have millions.

  • DATETIME(6) holds microseconds at the cost of 3 bytes over DATETIME. Is that precision needed.

  • decimal(16,5) takes 8 bytes.

  • How big is the table? SHOW TABLE STATUS LIKE 'products';. What is the value of SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; How much RAM do you have? (Maybe a partial fix is to adjust these.)

  • If things like current_stock are changed in realtime (vs overnight), move it out of this table so the Update won't be fighting with the table scan.

  • Move most of the "manufacturer" details out of this "attribute" table.

  • (See my other answer for FULLTEXT tip.

  • If most queries include main_category_id = 439, then the following trick may help:

      PRIMARY KEY(id),
      INDEX(main_category_id, ...)
      -->
      PRIMARY KEY(main_category_id, id),  -- to cluster rows together by category
      INDEX(id)  -- to keep AUTO_INCREMENT happy
    
2
  • The result to show table status like 'products'; is pastebin.com/PXjxgHcS The result to SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; 134217728 Jul 25, 2023 at 6:02
  • I know I have designed myself a neat little trap :D. I am open to splitting the table into multiple tables. FULL TEXT idea sounds good. I will try that and update. Thank you Jul 25, 2023 at 6:04
0
SELECT  count(`products`.`id`)
    FROM  `products`
    WHERE  (products.main_category_id = 439
              and  ( attr_3 in ('400mW','250mW') )
              and  ( attr_9 in ('7') )
           );

This index will make that run fast:

INDEX(main_category_id, attr_9, attr_3)
  • The leftmost columns are being tested with =. (IN of a single value is optimized as =).
  • The index is "covering".

There is simply no way to achieve the generality of 128 attributes and have speed.

What can be done?

  • Toss all the string attributes into a single column and have a FULLTEXT index and use syntax like MATCH(strings) AGAINST("+400mW +250mW" IN BOOLEAN MODE).
  • Use an Entity-Atribute-Value schema design. It has many flaws; see the tag I added.
  • Split things into several tables; JOIN them together as needed. But do not expect to have a single template for SELECT. Instead, build it on the fly.

More on indexing: Index Cookbook
More on EAV: Entity-Attribute-Value

(For further discussion, please provide SHOW CREATE TABLE inside the question.)

More

WHERE MATCH(combined_attrs) AGAINST('+400mW +250mW +8' in boolean mode)

+8 is a contradiction and, I claim, it is a bug. + says "must have" but 8 is excluded not included in the FT inverse index since it is too short. When I implemented a similar FT query based on arbitrary user input, I added + only to "words" of 3 or more characters.

This is, however, and example of where FT will not do "everything" you need. Also note the existence of "stopwords", but that can be disabled. I'm not sure, but FT may not index words that occur in more than half of the rows.

I don't trust the performance of IN (SELECT ...); try this:

SELECT  COUNT(*)
    FROM  product_filter_caches AS pfc
    JOIN  products AS p  USING(product_id)
    WHERE  MATCH(pfc.combined_attrs)
           AGAINST('+400mW +250mW 8' in boolean mode)
      AND  p.main_category_id = 439 ;

It might be better if you put the two tables together. I assume there is a 1:1 relationship between them?

11
  • Entity Attribute Value method was what I tried at first, But I needed to sort products based on attribute values. For eg: sort products based on operational power. I had to split the querying into two or more parts to get the results that I needed. I combined everything into one table just to make sure the sorting and filtering can be handled in one DB call. Moreover, wouldn't joins be slow if the tables are large. Or will indexes help with the joins? Jul 25, 2023 at 5:55
  • I will try the FULL TEXT index idea. Thank you. Jul 25, 2023 at 6:04
  • INDEXes (either explicit or implied by FOREIGN KEYs or PRIMARY KEYs) are necessary for JOINs. Joins let you go beyond 16 indexes. However, when the Joins become "too complex", performance become really bad. Can you provide different example queries. Please use reasonably realistic values. (As you did with "400mW".)
    – Rick James
    Jul 25, 2023 at 23:18
  • I tried the full text index idea by creating a copy of the table with one text field combined_attrs. I created a full text index on the combined_attrs. I ran the following select count(products.id) from products where products.main_category_id = 439 and id in (select product_id from product_filter_caches where match(combined_attrs) against('+400mW +250mW +8' in boolean mode)); The query executed in under 1 seconds, but the result Is not as expected. The actual result should be 190 but Im getting 0 Wouldn't 8 be excluded from full text search since its too short? Jul 26, 2023 at 4:18
  • I read that mariadb has a minimum token / word length which is 4 as per https://mariadb.com/kb/en/full-text-index-overview/. The link also talks about reducing the token size by configuring it so. Wouldn't that decrease the performance? Since the DBMS has to go through so many more tokens? Jul 26, 2023 at 4:27

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.