# How to speed up a simple query?

**URL:** <https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283>\
**Category:** Other MySQL® Questions\
**Created:** [October 30, 2009, 2:15pm UTC](https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283 "2009-10-30T14:15:45Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![sql\_er](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@sql\_er](https://forums.percona.com/u/sql_er)\
**Post date:** [October 30, 2009, 2:15pm UTC](https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283/1 "2009-10-30T14:15:45Z")

</div>

Hello,

We have the following 2 tables (and indexes) in our database:

## Table 1

CREATE TABLE linkentityarticle(id BIGINT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY, article\_id INT UNSIGNED NOT NULL, entity\_id INT UNSIGNED NOT NULL, score TINYINT UNSIGNED NOT NULL) Engine = InnoDB;

## Indexes

1. entity\_id
2. entity\_id + article\_id
3. article\_id + entity\_id
4. score

Total # rows in table: 10 million  
Total space used by table: 1.5 GB

## Table 2

CREATE TABLE entity(id INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY, node VARCHAR(255)) Engine = InnoDB;

## Indexes

1. node

Total # rows in table: 600,000  
Total space used by table: 88 MB

When I run the following query against these tables, it runs for over 30 seconds:

```
SELECT DISTINCT article_id

```

FROM linkentityarticle d  
INNER JOIN  
(  
SELECT id FROM entity  
WHERE node IN(‘sports’)

) AS T1  
ON T1.id = d.entity\_id  
ORDER BY d.article\_id DESC  
LIMIT 50;

If I remove ORDER BY d.article\_id DESC, it takes 0.3 seconds (i.e. 100 fold improvement).

Below is the EXPLAIN PLAN WITH ORDER BY

id: 1  
select\_type: PRIMARY  
table:   
type: system  
possible\_keys: NULL  
key: NULL  
key\_len: NULL  
ref: NULL  
rows: 1  
Extra: Using temporary; Using filesort  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: PRIMARY  
table: d  
type: ref  
possible\_keys: linkentityarticle\_entity\_id,index\_linkentityarticle\_entity\_i d\_article\_id  
key: linkentityarticle\_entity\_id  
key\_len: 4  
ref: const  
rows: 6708  
Extra: Using where  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 2  
select\_type: DERIVED  
table: drift\_entity  
type: const  
possible\_keys: unique\_entity\_node  
key: unique\_entity\_node  
key\_len: 257  
ref:  
rows: 1  
Extra: Using index

Below is the EXPLAIN WITHOUT ORDER BY:

id: 1  
select\_type: PRIMARY  
table:   
type: system  
possible\_keys: NULL  
key: NULL  
key\_len: NULL  
ref: NULL  
rows: 1  
Extra: Using temporary  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: PRIMARY  
table: d  
type: ref  
possible\_keys: linkentityarticle\_entity\_id,index\_linkentityarticle\_entity\_i d\_article\_id  
key: linkentityarticle\_entity\_id  
key\_len: 4  
ref: const  
rows: 6708  
Extra:  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 2  
select\_type: DERIVED  
table: drift\_entity  
type: const  
possible\_keys: unique\_entity\_node  
key: unique\_entity\_node  
key\_len: 257  
ref:  
rows: 1  
Extra: Using index

We are using MySQL version 5.0.44 and our InnoDB Buffer Pool is set to 2GB.

Is there anything that can be done to speed up this query when ORDER BY is used (as we need to use it to get the latest data).

Thank you in advance!

---

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [October 30, 2009, 6:48pm UTC](https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283/2 "2009-10-30T18:48:29Z")

</div>

If you omit the sort and limit, how many rows are in the result set? It should be quite a few if sorting takes that long. You should also be able to re-write that query to remove the inner select in favor of an inner join, though I’m not sure if that will help any, but you should get a different query plan out of EXPLAIN.

Before doing anything else, seriously consider switching that BIGINT to INT. It will shrink the size of the table and all the indexes considerably, and make everything you do with that table faster.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [October 31, 2009, 1:43pm UTC](https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283/3 "2009-10-31T13:43:03Z")

</div>

LIMIT makes your second query fast. Your first index is redundant since it is contained in your second index. I would change the lay-out of your first table to make use of a clustered index:

## Table 1

CREATE TABLE linkentityarticle(  
article\_id INT UNSIGNED NOT NULL,  
entity\_id INT UNSIGNED NOT NULL,  
score TINYINT UNSIGNED NOT NULL)  
Engine = InnoDB;

## Indexes

1. UNIQUE article\_id+entity\_id
2. entity\_id + article\_id
3. score

## Table 2

CREATE TABLE entity(  
id INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY,  
node VARCHAR(255)  
) Engine = InnoDB;

## Indexes

1. node

SELECT DISTINCT article\_id  
FROM linkentityarticle d  
WHERE id IN(SELECT id FROM entity e WHERE node IN(‘sports’) AND e.id = d.entity\_id)  
ORDER BY d.article\_id DESC  
LIMIT 50;

To check whether this helps, try this query first:

SELECT DISTINCT article\_id  
FROM linkentityarticle d FORCE INDEX(name of article\_id + entity\_id index)  
WHERE id IN(SELECT id FROM entity e WHERE node IN(‘sports’) AND e.id = d.entity\_id)  
ORDER BY d.article\_id DESC  
LIMIT 50;

---

<div class="post-metadata">

**Author:** ![sql\_er](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@sql\_er](https://forums.percona.com/u/sql_er)\
**Post date:** [November 4, 2009, 12:26pm UTC](https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283/4 "2009-11-04T12:26:47Z")

</div>

vgatto,

- Ommitting LIMIT I get 94383 rows as a result
- Rewriting the query to use the INNER JOIN I do get a slightly different explain plan, but the performance of the query did not improve
- I don’t prefer to change BIGINT to INT, as we might run out of integer space. We had it as INT in another system, and we ran out of space in ~ 2 years, and had to change to BIGINT. So although we could keep it as INT for some time, I prefer to come up with some permanent solution to this query optimization (i.e. not something which would only last until we run out of INT space)

gmouse,

- You are right. First index was redundant. I dropped it, but it did not help the speed of the query

- I cannot change the layout of the table, as there are many stored procedures which use the id field we have in this table

It seems to me that filesort is slowing down the query. Is there anything that can be done to make this sorting faster?

Thanks a lot!

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [November 4, 2009, 1:09pm UTC](https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283/5 "2009-11-04T13:09:39Z")

</div>

What about

SELECT DISTINCT article\_id  
FROM linkentityarticle d FORCE INDEX(name of article\_id + entity\_id index)  
WHERE id IN(SELECT id FROM entity e WHERE node IN(‘sports’) AND e.id = d.entity\_id)  
ORDER BY d.article\_id DESC  
LIMIT 50;

+explain

---

<div class="post-metadata">

**Author:** ![sql\_er](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@sql\_er](https://forums.percona.com/u/sql_er)\
**Post date:** [November 4, 2009, 1:24pm UTC](https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283/6 "2009-11-04T13:24:55Z")

</div>

gmouse,

I tried the suggestion, and performance got worse. It now ran for 1 min and 12 seconds. Rows field shows that it searched through the whole table now [Interestingly, it did not use filesort now]. Below is the explain:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: PRIMARY  
table: d  
type: index  
possible\_keys: NULL  
key: unique\_linkentityarticle\_article\_id\_entity\_id  
key\_len: 8  
ref: NULL  
rows: 9913948  
Extra: Using where; Using index  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 2  
select\_type: DEPENDENT SUBQUERY  
table: e  
type: const  
possible\_keys: PRIMARY,unique\_entity\_node  
key: unique\_entity\_node  
key\_len: 257  
ref: const  
rows: 1  
Extra: Using index

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [November 4, 2009, 2:24pm UTC](https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283/7 "2009-11-04T14:24:08Z")

</div>

I see. Actually it stops after 50 rows are found but appearantly this can take a very long time for seldomly used node names. The only way to improve running time is by having an index on (node,article\_id), but then you must have a table that contains both these fields.

---

<div class="post-metadata">

**Author:** ![sql\_er](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@sql\_er](https://forums.percona.com/u/sql_er)\
**Post date:** [November 4, 2009, 3:29pm UTC](https://forums.percona.com/t/how-to-speed-up-a-simple-query/1283/8 "2009-11-04T15:29:39Z")

</div>

gmouse,

Thank you for the suggestions.

I am currently creating such a table - just to test things out - if results are good I’ll consider implementing it in production.