вот пример запроса к таблице в которой 20 000 000 записей


SELECT *
FROM `Cat_Claim`
WHERE `townfrom` =2
AND `state` =62
GROUP BY Nights
PROCEDURE ANALYSE ( );

структура

CREATE TABLE IF NOT EXISTS `Cat_Claim` (
`Inc` int(11) NOT NULL,
`Catalog` int(11) NOT NULL,
`CheckIn` date NOT NULL,
`Nights` int(11) NOT NULL,
`Tour` int(11) NOT NULL,
`Spog` int(11) NOT NULL,
`Price` float NOT NULL,
`Currency` int(11) NOT NULL,
`InternetInvisible` int(1) NOT NULL,
`PeopleCount` int(11) NOT NULL,
`Hotel` int(11) NOT NULL,
`HtPlace` int(11) NOT NULL,
`Meal` int(11) NOT NULL,
`Room` int(11) NOT NULL,
`DateOut` date NOT NULL,
`townfrom` int(11) NOT NULL,
`state` int(11) NOT NULL,
`adult` tinyint(11) NOT NULL,
`child` tinyint(11) NOT NULL,
`packet_type` int(11) NOT NULL,
`crc_packet` int(11) NOT NULL,
`PType` int(11) NOT NULL,
`revision` int(11) NOT NULL,
`rev_value` int(11) NOT NULL,
PRIMARY KEY (`Inc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

в поле Optimal_fieldtype все уникальны значения пречислены, а самое главное запрос очень легкий.

Какие есть еще варианты кроме как данный трюк? (в этом варианте не нравиться то, что распарсивать данные из поля не очень удобно, + может кто знает еще более быстрый и лучший способ.)

tanglir,

запрос который выводит список уникальных значений в каждом поле

вот кусок выдачи

обратите внимание на ENUM('2014-10-25','2014-12-10','2014-12-13','2014-12-17','2014-12-20','2015-01-16','2015-02-04','2015-02-06','2015-03-04','2015-03-13')

список уникальных дат
и вот нужно получить такой список по каждому полю

mysql> SELECT *
-> FROM `Cat_Claim`
-> WHERE `townfrom` =2
-> AND `state` =62
-> GROUP BY Nights
-> PROCEDURE ANALYSE ( ) ;
+---------------------------------------+------------+------------+------------+------------+------------------+-------+-------------------------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std | Optimal_fieldtype |
+---------------------------------------+------------+------------+------------+------------+------------------+-------+-------------------------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tour_test.Cat_Claim.Inc | 658375581 | 659993204 | 9 | 9 | 0 | 0 | 659818371.0769 | 660764333.4244 | ENUM('658375581','659833735','659858412','659861775','659872140','659904627','659989065','659989105','659989145','659989265','659991305','659991465','659993204') NOT NULL |
| tour_test.Cat_Claim.Catalog | 6093 | 6104 | 4 | 4 | 0 | 0 | 6099.2308 | 5.1762 | ENUM('6093','6094','6095','6104') NOT NULL |
| tour_test.Cat_Claim.CheckIn | 2014-10-25 | 2015-03-13 | 10 | 10 | 0 | 0 | 10.0000 | NULL | ENUM('2014-10-25','2014-12-10','2014-12-13','2014-12-17','2014-12-20','2015-01-16','2015-02-04','2015-02-06','2015-03-04','2015-03-13') NOT NULL |
| tour_test.Cat_Claim.Nights | 9 | 20 | 1 | 2 | 0 | 0 | 14.9231 | 3.6260 | ENUM('9','10','11','12','13','14','15','16','17','18','19','20') NOT NULL |
| tour_test.Cat_Claim.Tour | 229 | 229 | 3 | 3 | 0 | 0 | 229.0000 | 0.0000 | ENUM('229') NOT NULL |
| tour_test.Cat_Claim.Spog | 2284 | 2540 | 4 | 4 | 0 | 0 | 2464.6154 | 105.2415 | ENUM('2284','2437','2534','2540') NOT NULL |
| tour_test.Cat_Claim.Price | 2075 | 5739 | 4 | 4 | 0 | 0 | 3457.153846153846 | 1153.6603850456459 | ENUM('2075','2143','2345','2396','3041','3105','3233','3247','3522','3886','4904','5307','5739') NOT NULL |
| tour_test.Cat_Claim.Currency | 2 | 2 | 1 | 1 | 0 | 0 | 2.0000 | 0.0000 | ENUM('2') NOT NULL |
| tour_test.Cat_Claim.InternetInvisible | 0 | 0 | 1 | 1 | 13 | 0 | 0.0000 | 0.0000 | ENUM('0') NOT NULL |
| tour_test.Cat_Claim.PeopleCount | 2 | 3 | 1 | 1 | 0 | 0 | 2.9231 | 0.2665 | ENUM('2','3') NOT NULL |
| tour_test.Cat_Claim.Hotel | 2618 | 3626 | 4 | 4 | 0 | 0 | 3164.5385 | 498.4795 | ENUM('2618','2629','2645','3626') NOT NULL |

MasterZiv,

была ошибка.
изменил запрос (убрал Group)

SELECT * FROM `Cat_Claim` WHERE `townfrom` =2 AND `state` =62 PROCEDURE ANALYSE ( );


mysql> SELECT * FROM `Cat_Claim` WHERE `townfrom` =2 AND `state` =62 PROCEDURE ANALYSE ( );
+---------------------------------------+------------+------------+------------+------------+------------------+-------+-------------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Field_name | Min_value | Max_value | Min_length | Max_length | Empties_or_zeros | Nulls | Avg_value_or_avg_length | Std | Optimal_fieldtype |
+---------------------------------------+------------+------------+------------+------------+------------------+-------+-------------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tour_test.Cat_Claim.Inc | 627285869 | 659993724 | 9 | 9 | 0 | 0 | 659117193.2496 | 0.0000 | INT(9) UNSIGNED NOT NULL |
| tour_test.Cat_Claim.Catalog | 6062 | 6104 | 4 | 4 | 0 | 0 | 6089.3993 | 11.2458 | ENUM('6062','6093','6094','6095','6104') NOT NULL |
| tour_test.Cat_Claim.CheckIn | 2014-10-22 | 2015-03-28 | 10 | 10 | 0 | 0 | 10.0000 | NULL | ENUM('2014-10-22','2014-10-25','2014-10-29','2014-11-01','2014-11-05','2014-11-08','2014-11-12','2014-11-15','2014-11-19','2014-11-22','2014-11-26','2014-11-29','2014-12-03','2014-12-06','2014-12-10','2014-12-13','2014-12-17','2014-12-20','2014-12-24','2014-12-26','2014-12-27','2015-01-01','2015-01-02','2015-01-03','2015-01-07','2015-01-09','2015-01-10','2015-01-14','2015-01-16','2015-01-17','2015-01-21','2015-01-23','2015-01-24','2015-01-28','2015-01-30','2015-01-31','2015-02-04','2015-02-06','2015-02-07','2015-02-11','2015-02-13','2015-02-14','2015-02-18','2015-02-20','2015-02-21','2015-02-25','2015-02-27','2015-02-28','2015-03-04','2015-03-06','2015-03-07','2015-03-11','2015-03-13','2015-03-14','2015-03-18','2015-03-20','2015-03-21','2015-03-25','2015-03-27','2015-03-28') NOT NULL |
| tour_test.Cat_Claim.Nights | 8 | 20 | 1 | 2 | 0 | 0 | 13.0132 | 3.6650 | ENUM('8','9','10','11','12','13','14','15','16','17','18','19','20') NOT NULL |
| tour_test.Cat_Claim.Tour | 229 | 229 | 3 | 3 | 0 | 0 | 229.0000 | 0.0000 | ENUM('229') NOT NULL |
| tour_test.Cat_Claim.Spog | 2284 | 2540 | 4 | 4 | 0 | 0 | 2392.8968 | 77.0402 | ENUM('2284','2434','2437','2534','2540') NOT NULL |
| tour_test.Cat_Claim.Price | 570 | 24415 | 3 | 5 | 0 | 0 | 3377.258439448328 | 2073.449277073755 | SMALLINT(5) UNSIGNED NOT NULL |
| tour_test.Cat_Claim.Currency | 2 | 2 | 1 | 1 | 0 | 0 | 2.0000 | 0.0000 | ENUM('2') NOT NULL |
| tour_test.Cat_Claim.InternetInvisible | 0 | 0 | 1 | 1 | 264650 | 0 | 0.0000 | 0.0000 | ENUM('0') NOT NULL |
| tour_test.Cat_Claim.PeopleCount | 1 | 4 | 1 | 1 | 0 | 0 | 2.4081 | 0.8246 | ENUM('1','2','3','4') NOT NULL |
| tour_test.Cat_Claim.Hotel | 2618 | 3626 | 4 | 4 | 0 | 0 | 2894.6861 | 366.2327 | SMALLINT(4) UNSIGNED NOT NULL |
| tour_test.Cat_Claim.HtPlace | 6300 | 6916 | 4 | 4 | 0 | 0 | 6323.3741 | 20.2089 | ENUM('6300','6301','6304','6305','6309','6312','6313','6314','6315','6316','6317','6318','6319','6320','6322','6324','6325','6326','6327','6916') NOT NULL |
| tour_test.Cat_Claim.Meal | 4 | 176 | 1 | 3 | 0 | 0 | 13.6710 | 34.1484 | ENUM('4','5','7','141','161','176') NOT NULL |
| tour_test.Cat_Claim.Room | 8857 | 10336 | 4 | 5 | 0 | 0 | 9172.4573 | 448.7613 | SMALLINT(5) UNSIGNED NOT NULL |
| tour_test.Cat_Claim.DateOut | 2014-10-30 | 2015-04-05 | 10 | 10 | 0 | 0 | 10.0000 | NULL | ENUM('2014-10-30','2014-11-02','2014-11-06','2014-11-09','2014-11-13','2014-11-16','2014-11-20','2014-11-23','2014-11-27','2014-11-30','2014-12-04','2014-12-07','2014-12-11','2014-12-14','2014-12-18','2014-12-21','2014-12-25','2014-12-27','2014-12-28','2015-01-02','2015-01-03','2015-01-04','2015-01-08','2015-01-10','2015-01-11','2015-01-15','2015-01-17','2015-01-18','2015-01-22','2015-01-24','2015-01-25','2015-01-29','2015-01-31','2015-02-01','2015-02-05','2015-02-07','2015-02-08','2015-02-12','2015-02-14','2015-02-15','2015-02-19','2015-02-21','2015-02-22','2015-02-26','2015-02-28','2015-03-01','2015-03-05','2015-03-07','2015-03-08','2015-03-12','2015-03-14','2015-03-15','2015-03-19','2015-03-21','2015-03-22','2015-03-26','2015-03-28','2015-03-29','2015-04-02','2015-04-04','2015-04-05') NOT NULL |
| tour_test.Cat_Claim.townfrom | 2 | 2 | 1 | 1 | 0 | 0 | 2.0000 | 0.0000 | ENUM('2') NOT NULL |
| tour_test.Cat_Claim.state | 62 | 62 | 2 | 2 | 0 | 0 | 62.0000 | 0.0000 | ENUM('62') NOT NULL |
| tour_test.Cat_Claim.adult | 1 | 3 | 1 | 1 | 0 | 0 | 1.9896 | 0.6285 | ENUM('1','2','3') NOT NULL |
| tour_test.Cat_Claim.child | 1 | 2 | 1 | 1 | 158226 | 0 | 0.4185 | 0.5255 | ENUM('0','1','2') NOT NULL |
| tour_test.Cat_Claim.packet_type | 0 | 0 | 1 | 1 | 264650 | 0 | 0.0000 | 0.0000 | ENUM('0') NOT NULL |
| tour_test.Cat_Claim.crc_packet | 0 | 0 | 1 | 1 | 264650 | 0 | 0.0000 | 0.0000 | ENUM('0') NOT NULL |
| tour_test.Cat_Claim.PType | 89 | 89 | 2 | 2 | 0 | 0 | 89.0000 | 0.0000 | ENUM('89') NOT NULL |
| tour_test.Cat_Claim.revision | 0 | 0 | 1 | 1 | 264650 | 0 | 0.0000 | 0.0000 | ENUM('0') NOT NULL |
| tour_test.Cat_Claim.rev_value | 0 | 0 | 1 | 1 | 264650 | 0 | 0.0000 | 0.0000 | ENUM('0') NOT NULL |
+---------------------------------------+------------+------------+------------+------------+------------------+-------+-------------------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set (2.72 sec)

не надо ни какой группировки.
нужно просто получить все уникальные значения во всех полях кроме inc

tanglir,

мне и не надо 20 млн. мне надо из 20 млн. выбрать то что надо и дать все уникальные записи по всем полям.

вопрос в силе. как это удобней сделать?