# Millions of rows... optimization help needed **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [February 18, 2008, 2:18am UTC](https://forums.percona.com/t/millions-of-rows-optimization-help-needed/642 "2008-02-18T02:18:55Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![mrmk](https://avatars.discourse-cdn.com/v4/letter/m/85e7bf/32.png) [@mrmk](https://forums.percona.com/u/mrmk)\ **Post date:** [February 18, 2008, 2:18am UTC](https://forums.percona.com/t/millions-of-rows-optimization-help-needed/642/1 "2008-02-18T02:18:55Z")
The DB structure is very flexible, allowing for various types of widgets and each type having various fields/values associated. For the sake of this question, assume that there are 3 types of widgets: mysql\> SELECT \* FROM widgets\_types;±-------±--------------------±--------------------+| typeid | typename | modified |±-------±--------------------±--------------------+| 1 | X Widgets | 2008-02-05 09:57:37 | | 2 | Y Widgets | 2008-02-05 09:58:39 | | 3 | Z Widgets | 2008-02-05 09:59:29 | ±-------±--------------------±--------------------+3 rows in set (0.02 sec) There are various fields that can be associated with each widget type: mysql\> SELECT \* FROM widgets\_fields;±—±----------------------±------------+| id | fieldname | fieldtypeid |±—±----------------------±------------+| 1 | First Name | 001 | | 2 | Last Name | 001 | | 3 | Primary Phone | 001 | | 4 | E-mail | 001 | | 5 | Address | 001 | | 6 | City | 001 | | 7 | Zip/Postal Code | 001 | | 8 | State | 001 | ±—±----------------------±------------+8 rows in set (0.02 sec) Each widget type has a different set of fields associated with it. mysql\> SELECT \* FROM widgets\_types\_fields;±—±-------±--------±-------+| id | typeid | fieldid | active |±—±-------±--------±-------+| 1 | 1 | 1 | 1 || 2 | 1 | 2 | 1 || 3 | 1 | 3 | 1 || 12 | 2 | 1 | 1 || 13 | 2 | 2 | 1 || 15 | 2 | 5 | 1 || 16 | 2 | 8 | 1 || 23 | 3 | 1 | 1 || 24 | 3 | 2 | 1 || 25 | 3 | 3 | 1 || 26 | 3 | 6 | 1 || 27 | 3 | 8 | 1 |±—±-------±--------±-------+12 rows in set (0.21 sec) The dynamic capability of this DB structure needs to be retained as new widget types will get added and different fields will be associated with different widget types. \*\* due to the required flexibility, it won’t be possible to have a single table widget type with fixed columns for each field for that type \*\* mysql\> describe widgets;±-----------------±--------------------------±-----±----±------------------±---------------+| Field | Type | Null | Key | Default | Extra |±-----------------±--------------------------±-----±----±------------------±---------------+| id | int(8) unsigned | NO | PRI | NULL | auto\_increment | | typeid | int(8) unsigned | NO | MUL | 0 | | | start\_price | decimal(5,2) | NO | | NULL | | | current\_price | decimal(5,2) | NO | | NULL | | | sale\_price | decimal(5,2) | NO | | NULL | | | ts\_sold | int(10) unsigned zerofill | NO | | 0000000000 | | | modified | timestamp | NO | | CURRENT\_TIMESTAMP | | ±-----------------±--------------------------±-----±----±------------------±---------------+7 rows in set (0.00 sec)mysql\> show index from widgets;±--------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |±--------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+| widgets | 0 | PRIMARY | 1 | id | A | 2316349 | NULL | NULL | | BTREE | | | widgets | 1 | idx\_typeid | 1 | typeid | A | 18 | NULL | NULL | | BTREE | | ±--------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+2 rows in set (2.19 sec)mysql\> describe widget\_values;±---------±----------------±-----±----±------------------±---------------+| Field | Type | Null | Key | Default | Extra |±---------±----------------±-----±----±------------------±---------------+| id | int(8) unsigned | NO | PRI | NULL | auto\_increment | | widgetid | int(8) unsigned | NO | MUL | NULL | | | fieldid | int(8) unsigned | NO | MUL | NULL | | | valchar | varchar(255) | NO | | NULL | | | modified | timestamp | NO | | CURRENT\_TIMESTAMP | | ±---------±----------------±-----±----±------------------±---------------+5 rows in set (0.00 sec)mysql\> show index from widget\_values;±--------------±-----------±-----------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |±--------------±-----------±-----------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+| widget\_values | 0 | PRIMARY | 1 | id | A | 17488933 | NULL | NULL | | BTREE | | | widget\_values | 1 | idx\_widgetid\_fieldid | 1 | widgetid | A | 5829644 | NULL | NULL | | BTREE | | | widget\_values | 1 | idx\_widgetid\_fieldid | 2 | fieldid | A | 17488933 | NULL | NULL | | BTREE | | | widget\_values | 1 | idx\_fieldid\_valchar | 1 | fieldid | A | 17 | NULL | NULL | | BTREE | | | widget\_values | 1 | idx\_fieldid\_valchar | 2 | valchar | A | 5829644 | NULL | NULL | | BTREE | | | widget\_values | 1 | idx\_widgetid\_fieldid\_valchar | 1 | widgetid | A | 5829644 | NULL | NULL | | BTREE | | | widget\_values | 1 | idx\_widgetid\_fieldid\_valchar | 2 | fieldid | A | 17488933 | NULL | NULL | | BTREE | | | widget\_values | 1 | idx\_widgetid\_fieldid\_valchar | 3 | valchar | A | 17488933 | NULL | NULL | | BTREE | | ±--------------±-----------±-----------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+8 rows in set (17.89 sec) The problem: There are millions of rows in the widgets table, and even more in widget\_values. I need to be able to select matching widgets, such as: Scenario 1: - typeid = 3 - fieldid 8 has value ‘CA’ - fieldid 1 has value LIKE ‘%john%’ mysql\> EXPLAIN SELECT `widgetid` FROM `widget_values` WHERE `widgetid` IN (SELECT `widgetid` FROM `widget_values` WHERE `fieldid` = ‘8’ AND `valchar` = ‘CA’) AND `widgetid` IN (SELECT `widgetid` FROM `widgets` WHERE `typeid` = ‘3’) AND `fieldid` = ‘1’ AND `valchar` LIKE ‘%john%’;±—±-------------------±--------------±---------------±----------------------------------------------------------------------±----------------------±--------±-----------±--------±-------------------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±-------------------±--------------±---------------±----------------------------------------------------------------------±----------------------±--------±-----------±--------±-------------------------+| 1 | PRIMARY | widget\_values | ref | idx\_fieldid\_valchar | idx\_fieldid\_valchar | 4 | const | 5051360 | Using where | | 3 | DEPENDENT SUBQUERY | widgets | ref | idx\_typeid | idx\_typeid | 4 | const | 917832 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | widget\_values | index\_subquery | idx\_widgetid\_fieldid,idx\_fieldid\_valchar,idx\_widgetid\_fieldid\_valchar | idx\_widgetid\_fieldid | 8 | func,const | 1 | Using where | ±—±-------------------±--------------±---------------±----------------------------------------------------------------------±----------------------±--------±-----------±--------±-------------------------+3 rows in set (0.32 sec) and running the actual query: 1887 rows in set (3 min 6.83 sec) SHOW INNODB STATUS while running the query showed this: view Scenario 2: - typeid = 3 - fieldid 7 = 90210 - fieldid 8 = ‘CA’ mysql\> EXPLAIN SELECT `widgetid` FROM `widget_values` WHERE `widgetid` IN (SELECT `widgetid` FROM `widget_values` WHERE `fieldid` = ‘8’ AND `valchar` = ‘CA’) AND `widgetid` IN (SELECT `widgetid` FROM `widgets` WHERE `typeid` = ‘3’) AND `fieldid` = ‘7’ AND `valchar` = ‘90210’;±—±-------------------±--------------±---------------±----------------------------------------------------------------------±----------------------±--------±------------±-------±-------------------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±-------------------±--------------±---------------±----------------------------------------------------------------------±----------------------±--------±------------±-------±-------------------------+| 1 | PRIMARY | widget\_values | ref | idx\_fieldid\_valchar | idx\_fieldid\_valchar | 261 | const,const | 464 | Using where | | 3 | DEPENDENT SUBQUERY | widgets | ref | idx\_typeid | idx\_typeid | 4 | const | 917832 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | widget\_values | index\_subquery | idx\_widgetid\_fieldid,idx\_fieldid\_valchar,idx\_widgetid\_fieldid\_valchar | idx\_widgetid\_fieldid | 8 | func,const | 1 | Using where | ±—±-------------------±--------------±---------------±----------------------------------------------------------------------±----------------------±--------±------------±-------±-------------------------+3 rows in set (0.60 sec) and running the actual query: 181 rows in set (0.10 sec) Scenario 3: - typeid = 3 - fieldid 1 LIKE ‘%angel%’ - fieldid 2 LIKE ‘%goff%’ mysql\> EXPLAIN SELECT lv1.widgetid FROM widget\_values AS lv1, widget\_values AS lv2, widgets WHERE widgets.typeid = 3 AND lv1.fieldid = 1 AND lv1.valchar LIKE ‘%angel%’ AND lv1.widgetid = widgets.id AND lv2.fieldid = 2 AND lv2.valchar LIKE ‘%goff%’ AND lv2.widgetid = widgets.id; ±—±------------±--------±-----±----------------------------------------------------------------------±---------------------±--------±-------------------------±-------±------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±--------±-----±----------------------------------------------------------------------±---------------------±--------±-------------------------±-------±------------+| 1 | SIMPLE | widgets | ref | PRIMARY,idx\_typeid | idx\_typeid | 4 | const | 917832 | Using index | | 1 | SIMPLE | lv1 | ref | idx\_widgetid\_fieldid,idx\_fieldid\_valchar,idx\_widgetid\_fieldid\_valchar | idx\_widgetid\_fieldid | 8 | widgetr.widgets.id,const | 1 | Using where | | 1 | SIMPLE | lv2 | ref | idx\_widgetid\_fieldid,idx\_fieldid\_valchar,idx\_widgetid\_fieldid\_valchar | idx\_widgetid\_fieldid | 8 | widgetr.widgets.id,const | 1 | Using where | ±—±------------±--------±-----±----------------------------------------------------------------------±---------------------±--------±-------------------------±-------±------------+3 rows in set (0.00 sec) and running the actual query: 47 rows in set (5 min 17.17 sec) The ultimate goal is to get the matching widgetid’s as fast as possible. Eagerly awaiting feedback from you all… Hardware: Q6600 2.4Ghz & 4GB memory show variables output: view Thanks in advance. ---
**Author:** ![mrmk](https://avatars.discourse-cdn.com/v4/letter/m/85e7bf/32.png) [@mrmk](https://forums.percona.com/u/mrmk)\ **Post date:** [February 18, 2008, 9:22pm UTC](https://forums.percona.com/t/millions-of-rows-optimization-help-needed/642/2 "2008-02-18T21:22:01Z")
Making some progress: EXPLAIN SELECT widgets.id FROM widgets INNER JOIN widget\_values lv1 ON lv1.widgetid = widgets.id INNER JOIN widget\_values lv2 ON lv2.widgetid = widgets.id WHERE widgets.typeid = 3 AND lv1.fieldid = 1 AND lv1.valchar LIKE ‘%b%’ AND lv2.fieldid = 2 AND lv2.valchar LIKE ‘%jo%’; ±—±------------±--------±-----±----------------------------------------------------------------------±---------------------±--------±---------------------------±-------±------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±--------±-----±----------------------------------------------------------------------±---------------------±--------±---------------------------±-------±------------+| 1 | SIMPLE | widgets | ref | PRIMARY,idx\_typeid | idx\_typeid | 4 | const | 917832 | Using index | | 1 | SIMPLE | lv1 | ref | idx\_widgetid\_fieldid,idx\_fieldid\_valchar,idx\_widgetid\_fieldid\_valchar | idx\_widgetid\_fieldid | 8 | widgetr.widgets.id,const | 1 | Using where | | 1 | SIMPLE | lv2 | ref | idx\_widgetid\_fieldid,idx\_fieldid\_valchar,idx\_widgetid\_fieldid\_valchar | idx\_widgetid\_fieldid | 8 | widgetr.lv1.widgetid,const | 1 | Using where | ±—±------------±--------±-----±----------------------------------------------------------------------±---------------------±--------±---------------------------±-------±------------+3 rows in set (0.01 sec) produces results in: 9584 rows in set (53.49 sec) 5min down to less than a min… not bad… but it’s possible to get better, isn’t it? Help! (please…) =) ---
**Author:** ![jonstjohn](https://avatars.discourse-cdn.com/v4/letter/j/dfb087/32.png) [@jonstjohn](https://forums.percona.com/u/jonstjohn)\ **Post date:** [February 23, 2008, 9:48am UTC](https://forums.percona.com/t/millions-of-rows-optimization-help-needed/642/3 "2008-02-23T09:48:05Z")
You may want to try to avoid using the ‘%term%’ syntax in favor of 1. Using ‘term%’ - it is very difficult to quickly do a search using a wild-card at the start of a search string. I’ve read that it is like looking for a string in the phone book. If you do not have a wild-card at the front, you can go straight go straight to the right place in the phone book. If you do have a wild-card, you have to scan every single entry - there is not a really efficient way to do it. The results will be different, but that might be acceptable to you. 2. Building a fulltext index and using the matching() using … syntax for fulltext searches. This will only search on the whole word and vastly speed up your queries. Hope that helps - Jon