Specifically, I have the following (anonymized) query showing up in my Slow Query log:
Query_time: 41 Lock_time: 0 Rows_sent: 1 Rows_examined: 3421238 SELECT count(*) FROM Table_A LEFT JOIN Table_B ON Table_A.B_id=Table_B.id LEFT JOIN Table_C ON Table_A.C_id=Table_C.id WHERE Table_A.deleted=0;
If it’s not obvious, this is an automatically generated query behind a page that generates a paginated result set, while showing something like “Displaying 1 - 20 of 40,678”.
The program’s method of getting that count is to strip out the field selection and replace it with the count(*), but leaving the WHERE portion in tact.
When the result set is filtered, say, by Date Range or other parameters, the query executes at an (almost) acceptable speed, because of proper indexing, etc.
But when users clear search terms, or first access the given page (with no search filters set), it simply paginates all results that aren’t (soft-) deleted.
Here’s some facts:
- Table_A is 495MB data, 160MB indexes, ~ 1,093,967 rows.
- Table_B is 388kB data, 14kB indexes, ~ 88 rows.
- Table_C is 130MB data, 11MB indexes, ~ 84,481 rows.
The EXPLAIN result for this query is:
±—±------------±------------------±-------±---------------±---------------±--------±-------------------------------------------------±--------±------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------------------±-------±---------------±---------------±--------±-------------------------------------------------±--------±------------+| 1 | SIMPLE | Table_A | ref | idx_a | idx_a | 1 | const | 1093964 | || 1 | SIMPLE | Table_B | eq_ref | PRIMARY | PRIMARY | 36 | db.Table_A.B_id | 1 | Using index || 1 | SIMPLE | Table_C | eq_ref | PRIMARY | PRIMARY | 8 | db.Table_A.C_id | 1 | Using index |±—±------------±------------------±-------±---------------±---------------±--------±-------------------------------------------------±--------±------------+3 rows in set (0.00 sec)
You can see that “deleted” is indexed, and is the only index usable by that particular query.
The Machine:
… is a Dell Power Edge 1950 with 2 quad core Xeon 2.0GHz, and 8 GB of memory. It does practically nothing but run the MySQL server.
The Question:
Is it reasonable for this query to take ~30 seconds to execute (because of the sheer amount of data maybe)? ~15?
Is that unknowable from the information provided?
Our next step is to call in a “highly paid consultant”, because I’m out of answers/research, and I fall just short of knowing from experience if this is actually acceptable when dealing with a million+ rows of data and two left joins.