CODEWITHSUNDEEP
mysqldatabase
djt
djt

Reputation: 7535

Basic query in unexpectedly slow in MySQL

I am running a basic select on a table with 189,000 records. The table structure is:

items

id - primary key
ad_count - int, indexed
company_id - varchar, indexed
timestamps

the select query is:

select * 
from `items` 
where `company_id` is not null 
    and `ad_count` <= 100 
order by `ad_count` desc, `items`.`id` asc 
limit 50

On my production servers, just the MySQL portion of the execution takes 300 - 400ms

If I run an explain, I get:

select type: SIMPLE 
table: items    
type: range 
possible_keys: items_company_id_index,items_ad_count_index  
key: items_company_id_index 
key_len: 403    
ref: NULL   
rows: 94735 
Extra: Using index condition; Using where; Using filesort

When fetching this data in our application, we paginate it groups of 50, but the above query is "the first page"

I'm not too familiar with dissecting explain queries. Is there something I'm missing here?

Upvotes: 1

Views: 63

Answers (3)

Tom Shir
Tom Shir

Reputation: 482

An ORDER BY clause with different sorting order can cause the creation of temporary tables and filesort. MySQL below (and including) v5.7 doesn't handle such scenarios well at all, and there is actually no point in indexing the fields in the ORDER BY clause, as MySQL's optimizer will never use them. Therefore, if the application's requirements allow, it's best to use the same order for all columns in the ORDER BY clause.

So in this case:

order by `ad_count` desc, `items`.`id` asc

Will become:

order by `ad_count` desc, `items`.`id` desc

P.S, as a small tip to read more about - it seems that MySQL 8.0 is going to change things and these use cases might perform significantly better when it's released.

Upvotes: 1

Robert Kaa Frank
Robert Kaa Frank

Reputation: 53

I could be wrong here (depending on your sql version this could be faster) but try a Inner Join with your company table.

Like:

Select *
From items
INNER JOIN companies ON companies.id = items.company_id
 and items.ad_count <= 100
LIMIT 50;

because of your high indexcount building the btrees will slow down the database each time a new entry is inserted. Maybe remove the index of ad_count?! (this depends on how often you use that entry for queries)

Upvotes: 0

Barmar
Barmar

Reputation: 780724

Try replacing items_company_id_index with a multi-column index on (company_id, ad_count).

DROP INDEX items_company_id_index ON items;
CREATE INDEX items_company_id_ad_count_index ON items (company_id, ad_count);

This will allow it to use the index to test both conditions in the WHERE clause. Currently, it's using the index just to find non-null company_id, and then doing a full scan of those records to test ad_count. If most records have non-null company_id, it's scanning most of the table.

You don't need to retain the old index on just the company_id column, because a multi-column index is also an index on any prefix columns, because of the way B-trees work.

Upvotes: 0

Related Questions