CODEWITHSUNDEEP
mysqlsql
Jerry Jones
Jerry Jones

Reputation: 796

Optimized SELECT query in MySQL

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!

Upvotes: 1

Views: 262

Answers (9)

Arvin Verdad Ilustre
Arvin Verdad Ilustre

Reputation: 41

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.

Upvotes: -1

JodyT
JodyT

Reputation: 4412

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.

Upvotes: 0

Mats Magnem
Mats Magnem

Reputation: 1405

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.

Upvotes: 0

Saifuddin Sarker
Saifuddin Sarker

Reputation: 863

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.

Upvotes: 0

CloudyMarble
CloudyMarble

Reputation: 37566

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.

Upvotes: 0

Bart Friederichs
Bart Friederichs

Reputation: 33511

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!

Upvotes: 0

Floris Velleman
Floris Velleman

Reputation: 4888

 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.

Upvotes: 0

Arno 2501
Arno 2501

Reputation: 9397

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);

Upvotes: 3

Frederik Spang
Frederik Spang

Reputation: 3454

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.

Upvotes: 1

Related Questions