CODEWITHSUNDEEP
mysqlsqlselect
Mark Belli
Mark Belli

Reputation: 740

optimize SELECT query, knowing that we are dealing with a limited range

I am trying to include in a MYSQL SELECT query a limitation. My database is structured in a way, that if a record is found in column one then only 5000 max records with the same name can be found after that one.

Example:

mark
..mark repeated 5000 times
john
anna
..other millions of names

So in this table it would be more efficent to find the first Mark, and continue to search maximum 5000 rows down from that one.

Is it possible to do something like this?

Upvotes: 1

Views: 159

Answers (4)

Martijn
Martijn

Reputation: 12102

If you make sure that the column is properly indexed, MySQL will take care off optimisation for you. Edit: Thinking about it, I figured that this answer is only useful if I specify how to do that. user nobody beat me to the punch: CREATE INDEX name ON your_table(name) USING BTREE

This is exactly what database indexes are designed to do; this is what they are for. MySQL will use the index itself to optimise the search.

Upvotes: 0

nobody
nobody

Reputation: 10645

Just make a btree index on the name column:

CREATE INDEX name ON your_table(name) USING BTREE

and mysql will silently do exactly what you want each time it looks for a name.

Upvotes: 2

Manse
Manse

Reputation: 38147

Its actually quite difficult to understand your desired output .... but i think this might be heading in the right direction ?

(SELECT name
FROM table
WHERE name = 'mark'
LIMIT 5000)

UNION

(SELECT name
FROM table
WHERE name != 'mark'
ORDER BY name)

This will first get upto 5000 records with the first name as mark then get the remainder - you can add a limit to the second query if required ... using UNION

For performance you should ensure that the columns used by ORDER BY and WHERE are indexed accordingly

Upvotes: 0

Robin Castlin
Robin Castlin

Reputation: 10996

Try with:

SELECT name
FROM table
ORDER BY (name = 'mark') DESC
LIMIT 5000

Basicly you sort mark 1st then the rest follow up and gets limited.

Upvotes: 0

Related Questions