Optimizing select SQL - MySQL

Question

Plz help me on this

Table Structure

ID(primary key) Text

To find a text what should i write ?

select Text from table where ID=1

or

select Text from table where ID=1 limit 1

Another Table Structure

Parent Table
PID(p k) ID

Child Table
ID (p.k) PID(f.k)

what should i write to delete fast ?

delete from ChildTable where ID=1;

or

delete from ChildTable where ID=1 and pid=1;  [i know the PID]

squawknull · Accepted Answer · 2011-04-21 03:40:21Z

1

The first two statements are equivalent. It should never return more than one row, so adding limit 1 is redundant.

For the deletes, it shouldn't matter. Mysql should look up by ID first as it's the most efficient index. There should only be one row returned, and hence the PID column is redundant, and at worst could actually confuse mysql into using a less efficient query plan.

answered Apr 21, 2011 at 3:40

squawknull

5,2022 gold badges19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

zneak Over a year ago

I think you got the DELETE query wrong. ID is the primary key.

Sourav Over a year ago

i know limit 1 does not help anything but will it save some time too ? I think you got wrong on the 2nd query, plz read it again !

squawknull Over a year ago

You're right. I read ID and PID wrong. I updated my answer. No, limit 1 won't save any time if it's only going to try to return 1 row anyway. It doesn't do anything in your case.

sh_kamalh · Accepted Answer · 2011-04-21 06:27:00Z

1

Just use the queries with the primary key only.

answered Apr 21, 2011 at 6:27

sh_kamalh

3,9014 gold badges42 silver badges50 bronze badges

Collectives™ on Stack Overflow

Optimizing select SQL - MySQL

Table Structure

Another Table Structure

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table Structure

Another Table Structure

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related