Mysql – Why does this MySQL query take 25+ seconds

MySQLperformancequery-performance

The query is:

SELECT T.ID, T.A, T.T 
FROM  table1 AS T
WHERE (userID='333335') AND (category LIKE '1%') AND (T.T='You Ain\'t Much 
Fun' AND T.A='KEITH, TOBY') GROUP BY T.A, T.T

I have a BTREE index over 2 columns – userID, and another column which doesn't feature in the above query. The index is set up with userID being the first column.

The table is innoDB, and the total table size is around 70 million, but the rows that userID='333335' equals total only about 500,000.

If userID has an index on it, and that's the first column queried, shouldn't the rest of the query be able to breeze through 500,000 rows pretty quickly?

What is it that I'm overlooking please?

Thank you for your time and help.

Best Answer

Your index may be finding the records relatively quickly, but it has to perform a double lookup for every single one of those rows that it finds for that userId.

Try adding the following index:

(userId, A, T, category)

to table1.

This will be a covering index, and should yield extremely quick results.

Notice another contrast

The table count is 4636881
The EXPLAIN plan for the first query traversed 4000010 indexed_column keys. No need to read the last 636871 keys.
The EXPLAIN plan for the second query traversed 4636881 rows ordered by indexed_column. For every row picking up the non-indexed column out of the table, the indexed column (already sorted by the index) is looked up and comes along for the ride. The tmp table gets ordered by the indexed column, and mysqld then dismisses the first 4000000 rows, leaving 10 rows. All that interaction between table and index just for 10 rows is the bottleneck.

COMMON THINGS

In both instances, the query specfies the number of rows to traverse. Since the number of rows in the table is 4636881, we should readily expect a full scan. The contrast becomes apparent when the MySQL Query Optimizer decides where to perform the full scan.

The first query is referencing an indexed column only in the SELECT list and the WHERE clause. The MySQL Query Optimizer chooses to perform a full index scan with no need to contact the table since everything needed is in the index.
The second query is referencing an indexed column in the WHERE clause. However, it must reach out the table to retrieve the corresponding non-indexed column. The MySQL Query Optmizer was tipped off by the query that it must not use the index becasue of the number of rows it was expected to read. As a rule of thumb for any RDBMS, if more than 5% of a table has to be read to fulfill a query, the MySQL Query Optimizer will just throw the index 'under the bus' and do a full table scan.

Doing the math, here is what MySQL Query optimizer computes:

5% of 4636881 is 231844
The second query is commanded to read 4000000 rows, which is way higher than 231844
MySQL Query Optimizer realizes there would be too much interaction between table (because of the non-indexed column) and index (because of the indexed column) to get the needed data. It decides to read the table only (because both the indexed and non-indexed columns are residing together in the table) rather than bounce back and forth between them.

In my honest opinion, with the row count of the table, the present indexes the table has, and the number of rows prescribed by the query, the MySQL Query Optimizer made the correct decision.

RECOMMENDATION

Create this index

ALTER TABLE `table` ADD INDEX mynewndx (indexed_column,some_other_column);

and your second query will never touch the table again going forward. The MySQL Query Optimizer will behave quite differently when it sees this new index.

Mysql – Poor query performance in MySQL

I suggest you add an index on all the columns used in the query, with this order (actually the order matters for the first 2 columns only, the rest 5 can be in any order within the index):

 (tag_text, 
  tag_level, 
  knowledge_id, tag_order, tag_weight, 
  total_title_direct_words, 
  total_title_parenthesis_words)

The query should then use this index for both the WHERE and the DISTINCT (or group by) part. You probably need to change the query slightly, removing the DISTINCT and using GROUP BY (this acts like a hint to the MySQL optimizer.)

SELECT   knowledge_id,                      -- remove DISTINCT
         tag_order, 
         tag_weight, 
         total_title_direct_words, 
         total_title_parenthesis_words, 
         tag_level 
FROM   knowledge_tags 
WHERE  ( tag_text = 'washington' ) 
  AND  ( tag_level < 10 )
GROUP BY   tag_text,                        -- use the same order
           tag_level,                       -- as in
           knowledge_id,                    -- the index,          
           tag_order,                       -- all
           tag_weight,                      -- the
           total_title_direct_words,        -- way
           total_title_parenthesis_words ;  -- down

The EXPLAIN should then show:

Using where; Using index for group-by

If the result are good enough for your expectations, you can then drop the tags_by_word_text index.

Best Answer

Related Solutions

Mysql – Why does MySQL choose this execution plan

Notice another contrast

COMMON THINGS

RECOMMENDATION

Mysql – Poor query performance in MySQL

Related Question