slow MySQL query on 1 table with 2 fields

Question

I have the following queries:

SELECT id FROM gr_webimages; (~0.0040 sec, 500 records)

SELECT id, parents FROM gr_webimages;  (~0.0450 sec, 500 records)

As you can see the only difference is the "parents" field which is a varchar(255) type. ("id" int(10) primary key) The second query is more than 10 times slower and the server not using any index. Could anyone explain why it takes so long and how can I speed up this query?

Are you sure that for the first query the optimizer didn't use an index?, can you post both plans? — Lamak, Jan 13 '14 at 16:15
... to clarify, @dasblinkenlight means a _composite_ index on the two columns. — Michael Berkowski, Jan 13 '14 at 16:17
WOW! the "id, parents" index made a miracle here! 0.0005 sec! I'm so excited! Thanks @dasblinkenligh, if you post your answer here I will accept it straight away. — tcxbalage, Jan 13 '14 at 16:20
That's OK, you have a perfectly good answer to accept here - John posted before my comment, and he said essentially the same thing. Good luck with your project! — Sergey Kalinichenko, Jan 13 '14 at 16:25

John Chrysostom · Accepted Answer · 2014-01-13T16:21:28.673

3

The first query can actually run without even touching the table... all the information it needs is available in the primary key index, so it just runs through the index to get the info it needs. The second query must actually go and read from the table, which is a significantly larger read...

See here: http://publib.boulder.ibm.com/infocenter/soliddb/v6r3/index.jsp?topic=/com.ibm.swg.im.soliddb.sql.doc/doc/using.indexes.to.improve.query.performance.html

"If the query selects only the indexed column, the query can read the indexed column value directly from the index, rather than from the table."

edited Jan 13 '14 at 16:21

answered Jan 13 '14 at 16:16

John Chrysostom

3,973
1
34
50

How do you know about the indexes?. I mean, this **is probably** the right answer, but the question does says *the server not using any index*, that's why I asked for clarification about that – Lamak Jan 13 '14 at 16:17
Primary key is by definition an index, is it not? – John Chrysostom Jan 13 '14 at 16:19
I missed that part of the question (but I'm also not sure how MySQL treats primary keys actually), you are most likely right though – Lamak Jan 13 '14 at 16:20
http://stackoverflow.com/questions/1071180/is-the-primary-key-automatically-indexed-in-mysql – John Chrysostom Jan 13 '14 at 16:21
Cheers pal, this index-things is a real dead-zone to me :) – tcxbalage Jan 13 '14 at 16:30

slow MySQL query on 1 table with 2 fields

1 Answers1