CODEWITHSUNDEEP
mysqlperformance
3iguru
3iguru

Reputation: 21

Mysql query runs very slow when using order by

The following query takes 30 seconds to finish when having order by. Without order by it finish in 0.0035 seconds. I am already having an index on field "name". Field "id" is the primary key. I have 400,000 record in this table. Please help, what is wrong with the query when using order by.

SELECT * 
FROM users 
WHERE name IS NOT NULL 
AND name != '' 
AND ( status IS NULL OR status = '0' ) 
order by id desc 
limit 50

Update: (solution at the end ) Hi All, Thanks for the help. Below is some updates as you asked:

  1. Below is the output of explain.

    id select_type table type possible_keys key key_len ref rows Extra

    1 SIMPLE users range name name 258 NULL 226009 Using where; Using filesort

  2. Yes, there are around 20 fields in this table.

  3. Below are the indexes that I have:

    Keyname Type Cardinality Field

    PRIMARY PRIMARY 418099 id name INDEX 411049 name

Solution: It turns out the fields with null values are the reason. When making those 2 fields in the where condition to NOT NULL, it just takes .000x seconds. But the strange thing is, it increases to 29 seconds if I create an index of (status,name,id DESC) or (status,name,id).

Upvotes: 2

Views: 1979

Answers (2)

vbence
vbence

Reputation: 20333

You should definitely have compound index. A single one containing all the fields you need as DBMSs can not really use more than one index on a single query.

An OR clause is not really index-friendly, so if you can I recommend setting status to NOT NULL. I assume NULL does not have any different meaning from the zero number. This will help a lot to actually use the index.

I do not know how much name != '' is optimized. Semantically equal would be name > '' (meaning it is later in the alphabet), may be this also save you some CPU cycles.

Then you have to decide the order in which your columns appear. A rule of thumb could be cardinality, the possible values a field can have.

By this:

ALTER TABLE users ADD INDEX order1 (status, name, id DESC);

Edit

You don't need to delete indexes. MySQL will choose the best one very quickly and ignore the rest. They only cost disk space and some CPU cycles on UPDATEs. But if you do not need them in any circumstances you can remove them of course.

The long time is because the access to your table is slow. This is probably caused by dynamic length fields such as TEXT or BLOB. If you do not ALWAYS need these, you can move them to a twin auxiliary table like:

users (id, name, status, group_id)
profile (user_id, birthdate, gender, motto, cv)

This way the essential system-operations can be done with a restricted information about the user, and all the other stuff which is really content associated with the user only have to be used when it is really needed.

Edit2

You hint MySQL which index to use by specifying it (or more of them) like:

SELECT id, name FROM users USE INDEX (order1) WHERE name != '' and status = '0' ORDER BY id DESC

Upvotes: 5

Paul Sanwald
Paul Sanwald

Reputation: 11329

without having an explain it is hard to say, but most probably you also need an index on the "status" column. slowness on a single table query almost always comes down to the query doing a full table scan as opposed to using an index.

try doing:

explain SELECT * 
FROM users 
WHERE name IS NOT NULL 
AND name != '' 
AND ( status IS NULL OR status = '0' ) 
order by id desc 
limit 50

and post the output. you'll probably see it is doing a full table scan, because it doesn't have an index for status. here's some documentation on using "explain". If you want more background, this is a nice article on the kind of problem you are having.

Upvotes: 0

Related Questions