Optimized SELECT query in MySQL

Question

I have a very large number of rows in my table, table_1. Sometimes I just need to retrieve a particular row.

I assume, when I use SELECT query with WHERE clause, it loops through the very first row until it matches my requirement.

Is there any way to make the query jump to a particular row and then start from that row?

Example:

Suppose there are 50,000,000 rows and the id which I want to search for is 53750. What I need is: the search can start from 50000 so that it can save time for searching 49999 rows.

I don't know the exact term since I am not expert of SQL!

I think you need to make an index. With an clustered index on the id field your db engine will be able to go directly to the good record whithout looping. — Arno 2501
– Arno 2501, Commented Feb 27, 2013 at 7:45
Well, as I said I am not an expert, can you please elaborate it please? — Jerry Jones
– Jerry Jones, Commented Feb 27, 2013 at 7:46
Why not have it start at 53750 and only cycle one? But all kidding aside, you need 1) get some extra knowledge on databases and 2) create an index. Good luck! — Bart Friederichs
– Bart Friederichs, Commented Feb 27, 2013 at 7:51

Arno 2501 · Accepted Answer · 2013-02-27 07:48:03Z

3

You need to create an index : http://dev.mysql.com/doc/refman/5.1/en/create-index.html

ALTER TABLE_1 ADD UNIQUE INDEX (ID);

answered Feb 27, 2013 at 7:48

Arno 2501

9,5179 gold badges39 silver badges57 bronze badges

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

Comments

Frederik Spang · Accepted Answer · 2013-02-27 07:47:05Z

1

The way I understand it, you want to select a row with id 53750. If you have a field named id you could do this:

    SELECT * FROM table_1 WHERE id = 53750

Along with indexing the id field. That's the fastest way to do so. As far as I know.

answered Feb 27, 2013 at 7:47

Frederik Spang

3,4641 gold badge30 silver badges44 bronze badges

2 Comments

Lighthart Over a year ago

I believe OP wants to know how to start the query at a certain ID and then move upwards to find it (that is to say the id will be found at runtime), so I'm not sure your answer really helps in that regard. However, the index will certainly make the situation better.

Frederik Spang Over a year ago

My fault. I've misunderstood the question.

Floris Velleman · Accepted Answer · 2013-02-27 07:49:45Z

0

 ALTER table_1 ADD UNIQUE INDEX (<collumn>)

Would be a great first step if it has not been generated automatically. You can also use:

 EXPLAIN <your query here>

To see which kind of query works best in this case. Note that if you want to change the where statement (anywhere in the future) but see a returning value in there it will be a good idea to put an index on that aswell.

answered Feb 27, 2013 at 7:49

Floris Velleman

4,9084 gold badges32 silver badges47 bronze badges

Comments

Bart Friederichs · Accepted Answer · 2013-02-27 07:50:07Z

0

Create an index on the column you want to do the SELECT on:

CREATE INDEX index_1 ON table_1 (id);

Then, select the row just like you would before.

But also, please read up on databases, database design and optimization. Your question is full of false assumptions. Don't just copy and paste our answers verbatim. Get educated!

answered Feb 27, 2013 at 7:50

Bart Friederichs

33.8k16 gold badges112 silver badges219 bronze badges

Comments

CloudyMarble · Accepted Answer · 2013-02-27 07:50:17Z

0

There are several things to know about optimizing select queries like Range and Where clause Optimization, the documentation is pretty informative baout this issue, read the section: Optimizing SELECT Statements. Creating an index on the column you evaluate is very helpfull regarding performance too.

answered Feb 27, 2013 at 7:50

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

Comments

Saifuddin Sarker · Accepted Answer · 2013-02-27 07:50:20Z

0

One possible solution You can create View then query from view. here is details of creating view and obtain data from view

http://www.w3schools.com/sql/sql_view.asp

now you just split that huge number of rows into many view (i. e row 1-10000 in one view then 10001-20000 another view )

then query from view.

answered Feb 27, 2013 at 7:50

Saifuddin Sarker

8633 gold badges8 silver badges26 bronze badges

Comments

Mats Magnem · Accepted Answer · 2013-02-27 07:51:58Z

0

I am pretty sure that any SQL database with a little respect for themselves does not start looping from the first row to get the desired row. But I am also not sure how they makes it work, so I can't give an exact answer.

You could check out what's in your WHERE-clause and how the table is indexed. Do you have a proper primary key? Like using a numeric data type for that. Do you have indexes on more columns, that is used in your queries?

There is also alot to concider when installing the database server, like where to put the data and log files, how much memory to give the server and setting the growth. There's a lot you can do to tune your server.

answered Feb 27, 2013 at 7:51

Mats Magnem

1,4051 gold badge13 silver badges22 bronze badges

Comments

JodyT · Accepted Answer · 2013-02-27 09:18:52Z

0

You could try and split your tables in partitions

More about alter tables to add partitions

Selecting from a specific partition

In your case you could create a partition on ID for every 50.000 rows and when you want to skip the first 50.000 you just select from partition 2. How to do this ies explained quite well in the MySQL documentation.

edited Feb 27, 2013 at 9:18

answered Feb 27, 2013 at 9:10

JodyT

4,4222 gold badges22 silver badges31 bronze badges

Comments

ThiefMaster · Accepted Answer · 2013-02-27 12:56:33Z

-1

You may try simple as this one.

query = "SELECT * FROM tblname LIMIT 50000,0

i just tried it with phpmyadmin. WHERE the "50,000" is the starting row to look up.

EDIT : But if i we're you i wouldn't use this one, because it will lapses the 1 - 49999 records to search.

edited Feb 27, 2013 at 12:56

ThiefMaster

320k85 gold badges608 silver badges648 bronze badges

answered Feb 27, 2013 at 7:53

Arvin Verdad Ilustre

411 silver badge7 bronze badges

4 Comments

Bart Friederichs Over a year ago

And what if record with id 53750 is not after the 50,000th row, but before it? MySQL gives no guarantees on how records are stored.

Arvin Verdad Ilustre Over a year ago

As a i edit it, i wouldn't guarantee you to use that one. You may use binary search instead of linear search. Google it.

Bart Friederichs Over a year ago

Or you let MySQL do the searching. It is quite good at it, if you have designed your database well.

Arvin Verdad Ilustre Over a year ago

Linear Search - scanftree.com/Data_Structure/Linear-search Binary Search - algorithms.openmymind.net/search/binarysearch.html It will reduce your time for searching for id if you use some array. Check the link.

Collectives™ on Stack Overflow

Optimized SELECT query in MySQL

9 Answers 9

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related