mysql, extract unique values from columns

Question

I have this website where I would allow the user to effectively parse the mysql database. I would like to present on the website the list of possible unique values from each column and hit an execute button to retrieve the data.

Is there a way to extract the unique values from the columns in a database? quickly? Before I was thinking of having some mapping file created before hand. I would ideally like this system to be fast. Any thoughts would be appreciated.

Mörre · Accepted Answer · 2017-04-23 07:13:47Z

3

Like this?

select DISTINCT [column] from [table];

edited Apr 23, 2017 at 7:13

anon

answered Feb 28, 2011 at 15:07

Mörre

5,7536 gold badges40 silver badges68 bronze badges

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

9 Comments

Martin Smith Over a year ago

This doesn't meet the speed requirement. I assume they are asking about a way of having them precalculated in an index or something.

Mörre Over a year ago

He does not ask about speed and indexes but only about unique values. Please don't invent parts of the question :) The speed he refers to seems to be about a "scripted solution", seems to me the author simply did not know the DISTINCT keyword in SQL... seems he gave that answer an UP, too. Now I'm just not quite happy that the later response received the UP and mine did not :( :)

Gordon Over a year ago

what is the speed on this? what if my database is 1 million + rows

diagonalbatman Over a year ago

The speed is very dependant on your setup, hardware, Indexes etc. Can you provide any more information? You can also try this and show us the Explain plan, it is usually a good starting point to assess performance.

Mörre Over a year ago

What I take as a given: one has to have an index on all the columns that are named. That's true for any column ever named after "SELECT". Anyway, if you have that index the rest is up to your particular database, the hardware and its configuration - how much RAM is made available for certain types of cache, etc. I don't think one can give a general answer, it very much depends on the very concrete situation. One has to rely on the DB manufacturer to have implemented the best algorithm for that purpose, not much a DB user can do - if you WANT to execute that operation, you have to "pay" for it.

|

diagonalbatman · Accepted Answer · 2011-02-28 15:07:46Z

1

SELECT DISTINCT column_name FROM table_name;

answered Feb 28, 2011 at 15:07

diagonalbatman

18.1k3 gold badges33 silver badges31 bronze badges

Collectives™ on Stack Overflow

mysql, extract unique values from columns

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related