mysql静态表,MySQL是否可以针对提供的静态数据列表加入表？

Consider this table:

TABLE names

+-------+-------+-----+-------------+

| id | f_key |name | sort_metric |

+-------+-------+-----+-------------+

| 1 | 1 | a | 1 |

| 2 | 1 | b | 2 |

| 3 | 1 | c | 0 |

| 4 | 2 | d | 0 |

| 5 | 2 | e | 2 |

| 6 | 2 | f | 1 |

| 7 | 3 | g | 1 |

| 8 | 3 | h | 0 |

...

| 9999 | 2500 | zzz | 2 |

| 10000 | 2500 | zzz | 0 |

+-------+-------+-----+-------------+

There are nearly 10,000 rows in this table. I have a query, which returns the correct results, but appears to be begging for optimization.

The query returns the f_key and name from this table ordered by (sort_metric, id) for each f_key. This query is run quite often so I'd like to make it as efficient as possible.

SELECT

name_a.f_key, name_a.name

FROM (

SELECT

DISCTINCT f_key

FROM

names

WHERE

f_key IN ( 254, 257, ..., 273, 279 )

) f_keys

JOIN names names_a ON names_a.id = (

SELECT

names_b.id

FROM

names names_b

WHERE

names_b.f_key = f_keys.f_key

ORDER BY

sort_metric ASC, id ASC LIMIT 1

)

The count of items in the IN ( ... ) clause is 24 every time, but the items are not sequential, and change often. id is the primary key and I have additional indexes on (f_key) and (sort_metric, id).

In particular, the derived table SELECT DISTINCT f_key FROM names WHERE f_key IN ( 254, 257, ..., 273, 279 ) seems silly to me. Is there not a way to use a static provided list as a derived table more efficiently? I can't figure out how to do that. Anyone?

解决方案The count of items in the IN ( ... ) clause is 24 every time

Then using an 'IN clause is the way to go - but the query you've provided does a LOT of unnecessary work (does joining a query with a 'WHERE' predicate referencing across te jion actually work?????) unless I'm much mistaken, this should produce the same result:

SELECT names.f_key, names.name

FROM names

WHERE names.f_key IN ( 254, 257, ..., 273, 279 );

update

OK - I see the second query is retricted to select the first item from names for each f_key - in which case....

SELECT n1.f_key, n1.name

FROM names n1

WHERE n1.f_key IN ( 254, 257, ..., 273, 279 )

AND NOT EXISTS (

SELECT 1 FROM names n2

WHERE n2.f_key=n1.f_key

AND ((n2.sort_metric>n1.sort_metric)

OR (n2.sort_metric=n1.sort_metric

AND n2.id>n1.id))

)

...or use the max concat trick to eliminate the subselect...

SELECT n.f_key, SUBSTRING(

MIN(CONCAT(LPAD(sort_metric, 8, '0'),LPAD(id, 8, '0'), name)),

17) AS name

FROM names n

WHERE n.f_key IN ( 254, 257, ..., 273, 279 )

GROUP BY f_key