mysqlmaxminimummaximaminima

MySQL - Choose specified search's maximum value without LIMIT or JOIN

I'm very newbie with MySQL. I have found several ideas and codes here at Stack Overflow to get a specified maximum value. All of them was totally different, and I can't decide which is the best way. I can't use GREATEST or LEAST, as I know it's not working with has PK and/or has FK multiple tables. (On my final exam I have to use FK and PK too.)

Here is my own code:

SELECT Column
FROM table
WHERE Column =
(
    SELECT MAX(Column)
    FROM table
    WHERE Value = true
)

I found a lazy version to get the maximum value:

SELECT ColumnName
FROM table
WHERE Value = true
ORDER BY ColumnName DESC
LIMIT 1

As I know this first makes an order, which is more time than check all data once and get what is the smallest value.

I also found this sentence-like method which using JOIN:

select
 f.*
from
 foo f
inner join
(
 select min(id) as id from foo
) m on m.id = f.id;

But now I don't have multiple tables and I don't think I have to make a (temporary?) sub-table because of one query from one table.

Here is my task: Make a query which gas state of matter is discovered the last.

My only 1 table's data with columns: http://pastebin.com/raw/82zZ0rh2

If an element is gas state of matter then the Gas column's row's value is 1, else it's 0.

My example code is deal with it like this:

SELECT DiscoverYear, ElementName
FROM discoveries
WHERE DiscoverYear =
(
    SELECT MAX(DiscoverYear)
    FROM discoveries
    WHERE Gas = 1
)

Please imagine tons of data. What is the best way to get the maximum?

Solution

  • I think your question is this: Which gaseous element was discovered most recently?

    Your example solution is correct.

    SELECT DiscoverYear, ElementName
FROM discoveries
WHERE DiscoverYear =
(
    SELECT MAX(DiscoverYear)
    FROM discoveries
    WHERE Gas = 1
)

    If it happens that two elements were discovered in the same year, you'll get both, which is a reasonable outcome.

    You need SQL indexes to make this work with lots of data. Notice that the number of elements in the periodic table is a very small dataset and not worth your time to optimize.

    The inner query SELECT MAX(DiscoverYear) FROM discoveries WHERE Gas = 1 can be optimized by a compound index on (Gas, DiscoverYear). You create that with this command.

    ALTER TABLE discoveries ADD INDEX state_year (Gas, DiscoverYear)

    MySQL can satisfy this query using a loose index scan which is very efficient.

    The outer query can be made faster by an index on DiscoverYear.

    ALTER TABLE discoveries ADD INDEX state_year (DiscoverYear)

    Some might suggest a compound index on (DiscoverYear, ElementName). However, in your problem, your outer query only looks up one or a very few items.

    Read this for lots of ways of approaching such problems. http://use-the-index-luke/ One thing to avoid: putting lots of indexes on your tables just in case you need them.