如何在从mysql表中获取不同的列值时加快查询执行速度。 [英] How to speed up the query execution while getting distinct column values from mysql table.

问题描述

嗨frenz，我有一个问题是从表中缓慢加载不同的列值。我知道从数据库加载数据的替代方法要快得多。下面的查询需要1分半钟才能获取数据。请建议正确的方法。谢谢。



我尝试了什么：



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）;



这里表名采用包含的动态名称超过3800000行

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

推荐答案

创建包含所有值的单个查询

SELECT dISTINCT gender，country，dateformat，product_name，source FROM table



然后使用这样的返回值创建@tx：

@ t1 = returnedTable.DefaultView.ToTable（true，gender）;





更好的解决方案是将单独的表格性别，国家/地区，日期格式，产品和来源链接到主表，您可以获得2,10或50个值，而无需多次检索400万行。





如果你必须拥有那个巨大的表，至少要在你正在检索的字段上创建索引......但无论你做什么，你都将永远不会有良好的性能o设计不好。

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.

这篇关于如何在从mysql表中获取不同的列值时加快查询执行速度。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！