Sql select query optimization

Question

I have a table and it's structured the following way:

CREATE TABLE `table` (
  `type` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  + Some additional fields
  KEY `type` (`type`)
)

What's the most efficient way to write the following query: Select the item with the maximal value of some certain type, let's say 'type0'.

If it's possible, could you also explain, what happens beneath the query (I mean, something that can affect the resulting algorithmic complexity).

ysrb · Accepted Answer · 2011-11-01 11:05:09Z

3

I think it's

SQL Server:

SELECT TOP 1 *
FROM 'table'
WHERE type = 'type0'
ORDER BY 'value' DESCENDING

MySQL:

SELECT *
FROM 'table'
WHERE type = 'type0'
ORDER BY 'value' DESC
LIMIT 1

I guess. The most important part is that you have index on both 'type' and 'value'

Thanks @Andrew for pointing he is asking for MySQL. Cheers.

edited Nov 1, 2011 at 11:05

answered Nov 1, 2011 at 10:59

ysrb

6,7202 gold badges32 silver badges30 bronze badges

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

2 Comments

Andrew Over a year ago

It's tagged mysql, so use LIMIT 1 at the end not TOP 1

Tim Rogers Over a year ago

Adding indexes is optional. It will decrease performance of inserts, updates and deletes so is something that has to be evaluated based on how many rows you expect, how frequently you will run this query, and how frequently the table is updated.

ypercubeᵀᴹ · Accepted Answer · 2011-11-01 11:03:39Z

2

Finding just one row (with maximal value):

SELECT *
FROM tableX 
WHERE type = 'type0'
ORDER BY value DESC
LIMIT 1

Finding all rows with same (maximal) value:

SELECT *
FROM tableX 
WHERE type = 'type0'
  AND value =
      ( SELECT MAX(value)
        FROM tableX
        WHERE type = 'type0'
      )

And index on (type, value) (for InnoDB table) or on (type, value, PK) (for MyISAM table) will be useful.

answered Nov 1, 2011 at 11:03

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Collectives™ on Stack Overflow

Sql select query optimization

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related