How to speed up the query execution while getting distinct column values from mysql table.

Question

0.00/5 (No votes)

See more:

Hi frenz, I have a problem of slow loading of distinct column values from table.Can i know the alternative way to load the data from database much faster. The query below is taking 1 and half minute to fetch data. Please suggest the right way. Thank you.

What I have tried:

SET @t1 = CONCAT('SELECT DISTINCT gender FROM ', tablename);
SET @t2 = CONCAT('SELECT DISTINCT country FROM ',tablename);
SET @t3 = CONCAT('SELECT DISTINCT DATE_FORMAT(date_time,\'%Y-%m-%d\') as date_time FROM ', tablename,' where source=','\'',sourceslist, '\'');
SET @t4 = CONCAT('SELECT DISTINCT product_name FROM ', tablename);
SET @t5 = CONCAT('SELECT DISTINCT source FROM ', tablename);

here table name takes the dynamic name which is containing more than 3800000 rows

Posted 17-May-16 21:08pm

sandhya46

Updated 17-May-16 21:32pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Accepted Answer · 2016-05-17T21:32:00

Create single query with all the values
SELECT dISTINCT gender, country, dateformat, product_name, source FROM table

Then create @tx by using returned values like this:
@t1 = returnedTable.DefaultView.ToTable(true, "gender");

Better solution would be to have separate tables gender, countries, date_formats, products and sources which then would be linked to the main table and you could get 2, 10 or 50 values without having to retrieve 4 million rows multiple times.

If you absolutely have to have that huge table, at least create indexes on the fields you're retrieving...but whatever you do, you will never have good performance based on bad design.