Несколько результатов из БД, с учётом сортировки

#mysql #sql #база_данных #select #выборка

Имеется таблица book в которой около 50 записей.
Поля таблицы id(int),name(varchar),path(text),lang(int, value = 1,2,3).
lang=1 примерно 18 записей
lang=2 примерно 16 записей
lang=3 примерно 16 записей
Необходимо составить sql запрос, чтобы выбрать 3 книги, первая из которых это случайное
число из первых 18 записей, второе это тоже случайное число из вторых 16 записей, и
третья соответственно тоже случайное число из последних 16 записей

Что то на подобие такого: 
SELECT * FROM book 
            WHERE lang=1 ORDER BY RAND () LIMIT 1 
                                               AND 
            WHERE lang=2 ORDER BY RAND () LIMIT 1
                                               AND 
            WHERE lang=3 ORDER BY RAND () LIMIT 1;
    

Ответы

Ответ 1

Для ваших объемов данных подойдет очень простой запрос:

SELECT random_book.*
FROM book
LEFT JOIN (SELECT * FROM book ORDER BY RAND()) random_book ON (book.lang = random_book.lang)
GROUP BY random_book.lang


ORDER BY RAND() - это не быстро на больших обьемах данных.


Ответ 2

Для ваших объемов:

SELECT * FROM book WHERE lang=1 ORDER BY RAND() LIMIT 1
UNION
SELECT * FROM book WHERE lang=2 ORDER BY RAND() LIMIT 1
UNION
SELECT * FROM book WHERE lang=3 ORDER BY RAND() LIMIT 1


Для больших объемов используйте min(id), max(id), rand() и математику в where - будет
без order by rand

Есть ли разница при SELECT * или SELECT `some_column` в PHP

#php #mysql #база_данных #select #производительность

Интересует насколько это влияет на производительность запроса в PHP

все запросы я делаю просто SELECT * ... LIMIT 0,20 и он постоянно подтягивает значения
всех столбцов (их примерно до 15), но использую из них я максимум 6-7.

У меня все время стоит лимит, а в базе строк больше > 10000 вот интересно это сильно
влияет на скорость? а что если дальше продолжать еще делать JOIN'ы ?
    

Ответы

Ответ 1

В большинстве случаев морочиться на эту тему не нужно. Вопрос про * в запросах -
это такая trivia, которую нубы с придыханием рассказывают друг другу и которую можно
прочитать в идиотских списках "25 способов ускорить свой скрипт в 100500 раз".

Если удобно писать * и в базе нет блобов с гигазами варезов, а есть 15 несчастных
варчаров, то никто не умрет от звездочки. 

Плюсы использования звездочки:


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


Минусы использования звездочки:


большой потенциальный объем данных, передаваемых в пхп, может сказаться на производительности
если в запросе используется временная таблица, то наличие ненужных полей может катастрофически
отразиться на времени запроса, поскольку во временную таблицу будут записываться все
поля таблицы. Разумеется, лучше избегать запросов, которым требуется создание временной
таблицы.


Ответ 2

использование * = "bad practice". 
минусы использование * :
1)если вы будете дальнейшей писать JOIN-i тогда  у вас будет проблемы
2)если вы будете дальнейшей добавляет столбцы тогда они будет автоматический подключиться
"без нужды"
3)лишние данные это зло
4)использование не дает вам прав наименование столбцов
5)может быть меньше но влияет на загрузку

Как заполнить select из json, используя angular?

#jquery #php #select #angularjs

Добрый день.
Как можно из Json внести данные в ..., используя
angularjs (либо jquery)?
Есть json ответ типа 
[{"id":"5","Year":"2010"},{"id":"6","Year":"2010"},{"id":"7","Year":"2013"}]

Хочу внести его в select.
Спасибо.    

Ответы

Ответ 1

ng-options


    Select year



fiddle



option с ng-repeat


    Select year
    {{item.Year}}



fiddle



Предпочитать ng-options потому, что: 


можно выбрать другие типы, чем String (однако когда пишем value="{{item.id}}", тип
значения атрибута value всегда String)
производительность: не создаёт scope для каждого элемента коллекции
синтакс яснее


Ответ 2

Вам никто не мешает написать Ng-repeat внутри блока Select

  {{item}}


Пример в плунке

Как передать value селекта в скрипте ajaxupload

#javascript #jquery #ajax #select

Проблема вот в чем, есть ajax upload загрузка файлов, все работает, но хочу вместе
с файлом передать и value селекта в postData:, пробовал и так и сяк, не получается,
как правильно сделать, посоветуйте,
вот селект и скрипт загрузки
передает только тот селект который был выбран при загрузке страницы



$('document').ready(function ($, data) {
  
    $('.upload').upload({
        action: '/admin/upload',
        label: 'Перетащите файл или кликните для выбора',
        postKey: 'newfile',
        maxQueue: 1,
        postData: ({data: data = $('#my_select').val()}),
        maxSize: 300485760
    }).on("start.upload", Start)
            .on("filestart.upload", fileStart)
            .on("fileprogress.upload", fileProgress)
            .on("filecomplete.upload", filePComplelele)
            .on("fileerror.upload", fileError)
            .on("complete.upload", Complete);
});

     Список 1
     Список 2
     Список 3
     Список 4




    

Ответы

Ответ 1

Судя по вашему скрипту, запрос upload как раз и отправляется при загрузке страницы.
А на этот момент вполне логично, что по вашему селектору как раз и возвращается select,
установленный при загрузке.

Вызов upload необходимо привязать к чему то типа button click, и внутри обработчика
вычислять: 

$('#my_select').val();

Работа с AF_UNIX SOCK_DGRAM SO_BROADCAST сокетом

#c #сокет #unix #select #unix_socket

Здравствуйте, пишу чатик, начал с параллельного эхо-сервера.
Теперь пытаюсь сделать так, чтобы сообщения от одного подключенного клиента рассылались
всем подключенным клиентам.
В процессе-родителе создаю сокет в файловом пространстве, и делаю его широковещательным:
  int commfd;
  struct sockaddr commaddr;

  if( ( commfd = socket( AF_UNIX, SOCK_DGRAM, 0 ) ) < 0 )
    perror( "error in common socket" );

  bzero( &commaddr, sizeof( commaddr ) );
  commaddr.sa_family = AF_UNIX;
  strcpy( commaddr.sa_data, COMM_SOCK_NAME );

  if( bind( commfd, &commaddr, sizeof( commaddr ) ) < 0 )
    perror( "error in bind for commfd" );

  int n=1;
  if( setsockopt( commfd, SOL_SOCKET, SO_BROADCAST, &n, sizeof(n) ) < 0 )
    perror( "error in setsockopt" );

И в дочернем процессе-обработчике клиента подключаюсь к этому сокету, и через select
жду, пока сокет этот будет доступен на чтение. 
В общем суть задумки: клиент пишет сообщение серверу (а на сервере для этого клиента
свой процесс-обработчик), это сообщение отсылается назад этому же клиенту, и это же
сообщение пишется в широковещательный сокет. В это же время процессы - обработчики
других клиентов ждут того самого сообщения, принимают, и выводят на экран.
Но не срабатывает select в обработчике, что я не так делаю?
Весь код сервера здесь: http://paste.ubuntu.com/7617694/    

Ответы

Ответ 1

UNIX-cокеты не поддерживают широковещательную передачу. Приём-передача широковещательных
udp-пакетов осуществляется с использованием (кроме опции SO_BROADCAST) широковещательных
адресов, которые не предусмотрены в AF_UNIX. Так же невозможно забиндить более одного
сокета на один AF_UNIX-адрес, что потребовалось бы для приёма.

Выбор MYSQL записи по нескольким строкам с группировкой по ID [дубликат]

#mysql #sql #select

        
             
                
                    
                        
                            На этот вопрос уже даны ответы здесь:
                            
                        
                    
                
                        
                            Не могу составить запрос MySQL для фильтра по товарам
                                
                                    (2 ответа)
                                
                        
                                Закрыт 3 года назад.
            
                    
Приветствую.  

Есть MySQL таблица со следующей структурой:  

|id_a | id_b |  
|  1  |   1  |  
|  1  |   2  |  
|  1  |   3  |  
|  2  |   1  |  
|  2  |   4  |  
|  3  |   1  |
|  3  |   2  |  


Есть необходимость сделать выборку id_a с фильтром сразу по нескольким значениям id_b.
Например нужно выбрать все id_a где id_b = 1 И 2, , т.е так, что бы на выходе мы
получили id_a: 1, 3.
id_a - 2 нам не подходит т.к. у него ЕСТЬ id_b 1, но НЕТ id_b - 2, при этом нам подходит
id_a - 1, т.к. у него есть оба значения id_b, пусть даже есть еще третье значение id_b
- 3. 

Запрос 
SELECT id_a FROM table WHERE id_b = 1 AND id_b = 2 GROUP BY id_a;
не подходит, т.к. в этом случае MySQL ищет что бы в одной строке было сразу оба значения
id_b.
Есть идея сделать через GROUP_CONCAT объединив id_b в одну строку.
Например  

|id_a | id_b |  
|  1  |   1, 2, 3  |  
|  2  |   1, 4     |  
|  3  |   1,2      |


Но в таком случае придется делать запрос, в запросе, что значительно повысит нагрузку
на СУБД при выборке, чего допускать не желательно.

Буду благодарен любой Вашей помощи.
    

Ответы

Ответ 1

Можно попробовать такой запрос без группировки:

SELECT DISTINCT t1.id_a
FROM test t1 INNER JOIN (SELECT DISTINCT id_a FROM test WHERE id_b = 1) t2
     ON t1.id_a = t2.id_a
WHERE t1.id_b = 2


sqlfiddle

Вывод: 1, 2.


Ответ 2

и будет вам счастье

SELECT id_a FROM table WHERE id_b in (1, 2) GROUP BY id_a;

Подзапрос столбцов в Select

#sql #oracle #select

Мне необходимо в запросе вывести значения непустых столбцов (в таблице более 200
столбцов, но заполнены из них около 10).

SELECT (SELECT  t.column_name
FROM    user_tab_columns t
WHERE   t.nullable = 'Y'
        AND t.table_name = 'mytable'
        AND t.num_distinct != 0) FROM mytable;


Код возвращает ожидаемую ошибку: 


  ORA-01427: подзапрос одиночной строки возвращает более одной строки
  01427. 00000 -  "single-row subquery returns more than one row"


"Подзапрос" корректно возвращает список непустых колонок.
    

Ответы

Ответ 1

Подзапрос в листе SELECT может вернуть только один столбец и одну строку, т.е. подзапросом
достичь желаемого невозможно. Можно создать запрос динамиески в PL/SQL. Это наверное
единственная возможность построить запрос, если имена и количество столбцов незвестны.
Например так:    

declare
    cols varchar2(1999);
    cur sys_refcursor;
    curid number;
    type colvals_type is table of varchar2(32);
    colvals colvals_type := colvals_type();
    desctab  dbms_sql.desc_tab;
    colcnt number;
begin
    for col in (
        SELECT column_name
        FROM user_tab_columns 
        WHERE nullable = 'Y'
        AND table_name = upper('mytable')
        AND num_distinct != 0) loop
        cols := cols || col.column_name || ','; 
    end loop;
    cols := regexp_replace(cols, ',$', '');
    open cur for 'SELECT ' || cols || ' FROM mytable';
    curid := dbms_sql.to_cursor_number(cur);
    dbms_sql.describe_columns(curid, colcnt, desctab);
    for idx in 1..colcnt loop 
        colvals.extend; 
        dbms_sql.define_column(curid, idx, colvals(idx), 32); 
    end loop;
    dbms_output.put_line(regexp_replace(cols, ',', chr(9)));
    while dbms_sql.fetch_rows(curid) > 0 loop
        for idx  in 1..colcnt loop 
            dbms_sql.column_value(curid, idx, colvals(idx));
            dbms_output.put(colvals(idx) || chr(9));
            --desctab(idx) - вся инфа о столбце - имя, длина, тип и т.д
        end loop;
        dbms_output.new_line();
    end loop;
end;
/


PS в JAVA было бы по моему проще, хотя смысл тот же. Ну или, как автор предложил
в комментарии, генерировать каждый раз SQL-скрипт и выпполнять его. Всё зависит от
того, для чего это конкретно нужно.


Ответ 2

Начиная с релиза 18c был введён новый тип табличных функций - PFT (Polymorphic Table
Functions). Это даёт некоторые возможности упростить решение подобных задач как в вопросе.

Подготовим данные, причём заполним только два столбца - par1 и par5:

create table params (id number, par1 number, par2 number, par3 number, par4 number,
par5 number);
insert into params (id, par1, par5) 
    select rownum, rownum+10, rownum+50
    from xmlTable ('1 to 1000')
;
exec dbms_stats.gather_table_stats (user, 'params') 
create or replace type colNameList is table of varchar2 (130);
/  


Сам запрос и вывод результата:

select *
from skipCols.emptyByTableName (params, 'params')
where id <= 3
;

        ID       PAR1       PAR5
---------- ---------- ----------
         1         11         51
         2         12         52
         3         13         53


Реализация табличной функции:

create or replace package skipCols as
    function emptyByTableName (tab table, tableName varchar2) return table pipelined
row polymorphic using skipCols;
    function describe (tab in out dbms_tf.table_t, tableName varchar2) return dbms_tf.describe_t;
end skipCols ; 
/
create or replace package body skipCols as
    function describe (tab in out dbms_tf.table_t, tableName varchar2) return dbms_tf.describe_t as 
        cols colNameList;
    begin 
        select cast (collect ('"'||column_name||'"') as colNameList) into cols
        from user_tab_cols  
        where table_name = upper (tableName)
        and nullable = 'Y'
        and num_distinct = 0
        ;
        for ix in 1..tab.column.count loop
            tab.column(ix).pass_through := tab.column(ix).description.name not member
of cols;
        end loop;
        return null;
    end;
end skipCols;
/

Вывод значения при условии, что подобного нету в другой таблице

#mysql #sql #select

Нужно вывести информацию с db_order, если нету ни одного повторения значения с db_order.id
в таблице db_ips.order_id.

Сами таблицы:

db_ips


db_order


Спасибо.
    

Ответы

Ответ 1

Если я правильно понял ваш вопрос, то как то так:

SELECT
    db_order.*
FROM 
    db_order
    LEFT OUTER JOIN db_ips  ON db_order.id = db_ips.order_id
WHERE 
    db_ips.order_id IS NULL;


Ответ 2

1й способ:

SELECT db_order.*
  FROM db_order
  LEFT JOIN db_ips ON db_order.id = db_ips.order_id
 WHERE db_ips.order_id IS NULL


2й способ:

SELECT *
  FROM db_order
 WHERE NOT EXISTS(select 1 from db_ips where db_order.id = db_ips.order_id)


3й способ:

SELECT *
  FROM db_order
 WHERE id NOT IN (SELECT order_id FROM db_ips)


Скорость выполнения этих запросов может отличаться. Однозначного ответа, какой из
них будет быстрее, конкретно на ваших данных, сказать сложно. Это зависит от количества
записей в таблицах и количества совпадающих/отсутствующих значений.


Ответ 3

Можно поступить следующим образом

SELECT
  *
FROM
  db_order
WHERE
  id NOT IN (SELECT
              DISTINCT order_id
            FROM
              db_ips)

Как найти незанятые области через запрос MySql

#mysql #select

Приложил картинку:
в базе хранятся закрашенные области (серый цвет), как координаты левого верхнего
угла и правого нижнего, т.е. ЧЕТЫРЕ числа (х1,у1,х2,у2).
Как запросом в базу MySql получить координаты свободной области, если условия такие:  


начиная сверху слева найти свободную область размером 1х1  
начиная сверху слева найти свободную область размером 2х2  


То есть для п.1 ответ должен быть 0,0,1,1 (голубой квадратик)
для п.2 = 6,0,8,2 (оранжевый квадратик)  

 
Все, на что хватило знаний)) -это функцией MIN нахожу свободную координату по оси
X, но в совокупности с осью Y это неправильно. И для поиска областей 2х2 поиск через
MIN вообще не применим.
Как вариант, думал создать еще одну таблицу с перечислением всех возможных точек
(т.е. для данного квадрата это 10х10=100 строк) и ставить пометку - такие-то точки
заняты, а такие-то нет и искать среди незанятых, но если размер поля надо будет увеличить
(с 10х10 до например 100х100), то скорее всего начнутся задержки???
База:  



DROP TABLE IF EXISTS `m_items`;
CREATE TABLE `m_items` (
  `id` int(11) NOT NULL,
  `x1` smallint(4) NOT NULL,
  `y1` smallint(4) NOT NULL,
  `x2` smallint(4) NOT NULL,
  `y2` smallint(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;






INSERT INTO `m_items` (`id`, `x1`, `y1`, `x2`, `y2`) VALUES
(1, 3, 0, 6, 4),
(2, 8, 0, 9, 2),
(3, 0, 1, 2, 2),
(4, 0, 3, 2, 5),
(5, 7, 3, 9, 7),
(6, 1, 6, 6, 7),
(7, 1, 8, 2, 10);



    

Ответы

Ответ 1

Попробуйте так:

select x,y
from c -- см. ответ @mnv
left join m_items
on x <= X2-1 and
   x+2 >= X1 and
   y <= Y2-1 and
   y+2 >= Y1
where x+2<10 and y+2<10
and id is null
order by y, x;


+2 - это квадраты 3x3.

SQLFiddle


Ответ 2

Можно сделать так. Строим табличку всех возможных комбинаций x и y (от 1 до 10).
Чтобы проще воспринимался запрос для получения результата, оформляем это в виде временной
таблицы:

CREATE TEMPORARY TABLE IF NOT EXISTS c AS (
    SELECT x, y FROM (
        SELECT 0 as x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) x
    CROSS JOIN (
    SELECT 0 as y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
            UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) y
);


В @size положим размер свободного квадрата. Тут 0 означает квадрат размером 1x1.
Для квадрата 2x2 делаем так:

SET @size = 2 - 1;


Теперь все готово для того, чтобы найти ближайший свободный квадрат. Следующий запрос
находит все свободные квадраты, сортирует результаты в нужном порядке и выбирает первый,
что требуется по условию. Смысл запроса - поиск областей, которые не пересекаются с
тем, что имеется на поле:

SELECT * FROM c WHERE NOT EXISTS (
    SELECT 1 FROM t WHERE (
        (x >= x1 AND x <= x2) 
        OR (x + @size >= x1 AND x + @size <= x2)
        OR (x1 >= x AND x1 <= x + @size)
        OR (x2 >= x AND x2 <= x + @size)
    ) AND (
        (y >= y1 AND y <= y2) 
        OR (y + @size >= y1 AND y + @size <= y2)
        OR (y1 >= y AND y1 <= y + @size)
        OR (y2 >= y AND y2 <= y + @size)
    )
)
AND x < 10 - @size
AND y < 10 - @size
ORDER BY x, y
LIMIT 1;


Посмотреть пример и поэкспериментировать можно тут.

ОБНОВЛЕНИЕ

Запрос выше для случая, когда надо двигаться сперва по оси y, потом по x (так изначально
был поставлен вопрос). Если нужен ответ для отредактированного варианта вопроса, то
просто поменяйте условие сортировки:

ORDER BY y, x

Select таблицы с внешним ключем, который может иметь значение NULL

#sql #postgresql #select

Создаю две таблицы:

CREATE TABLE public.reader_speciality (
  speciality_id SMALLSERIAL,
  value text,
  CONSTRAINT reader_speciality_pkey PRIMARY KEY (speciality_id)
) WITH ( OIDS=FALSE );
ALTER TABLE public.reader_speciality
  OWNER TO ""{userID}"";

CREATE TABLE public.readers(
  reader_id SERIAL NOT NULL,
  fio text NOT NULL,
  speciality_id smallint,
  CONSTRAINT readers_speciality_id_fkey FOREIGN KEY(speciality_id)
    REFERENCES public.reader_speciality(speciality_id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
) WITH(OIDS= FALSE);
ALTER TABLE public.readers
  OWNER TO ""{userID}"";


readers имеет внешний ключ speciality_id. Добавляю записи:

INSERT INTO reader_speciality (value) VALUES ('Физика');
INSERT INTO reader_speciality (value) VALUES ('Химия');
INSERT INTO readers (fio, speciality_id) VALUES ('Иванов Иван Иванович', 1);
INSERT INTO readers (fio) VALUES ('Игорев Игорь Игоревич');


Смотрим (индексы другие, но это не суть):

SELECT * FROM reader_speciality;




SELECT * FROM readers;




Теперь пробую выдать вместо индекса значение внешнего ключа и что-то идёт не так:

SELECT readers.reader_id,
       readers.fio,
       reader_speciality.value
FROM   readers, reader_speciality
WHERE (readers.speciality_id = reader_speciality.speciality_id OR
       readers.speciality_id IS NULL);




Как правильно составить запрос в такой ситуации?
    

Ответы

Ответ 1

SQL всегда пытается для каждой записи из первой таблицы подобрать все записи из второй
таблицы. Обычно мы удерживаем его от этого желания задав условие связи двух таблиц.
А вот когда срабатывает OR readers.speciality_id IS NULL получается, что условие истинно
для любых записей из второй таблицы.

Для таких случаев в SQL служит LEFT JOIN, который подбирает записи если они есть
и возвращает запись из первой таблицы даже если во второй ничего нет.

SELECT readers.reader_id,
       readers.fio,
       reader_speciality.value
  FROM readers
  LEFT JOIN reader_speciality
    ON readers.speciality_id = reader_speciality.speciality_id

SELECT 1 - какой смысл этого запроса?

#select #sql

Какой смысл несет SQL запрос SELECT 1.

Знаю, что он возвращает единицу, но зачем это нужно?

Где его применяют на практике?
    

Ответы

Ответ 1

Ну например, что бы проверить, работает ли база вообще. Потому что если она этот
запрос не может обработать, то дела плохи.


Ответ 2

С помощью подобных запросов, можно почти бесплатно генерировать числовые последовательности...
Сам по себе - просто получить константу)
select
  n2.n * 4 + n1.n + 1 as n
from
  ( select n = 0 union all select 1 union all select 2 union all select 3 ) n1,
  ( select n = 0 union all select 1 union all select 2 union all select 3 ) n2


Ответ 3

Вообще говоря, это вычисляемый столбец: select <выражение>,...
А так в зависимости от контекста. 
Например, с предикатом [NOT] EXISTS, т.к. выводить что-нибудь в списке столбцов смысла
не имеет.

Медленный запрос count(*)

Я решил немного поиграться с относительно большими объемами данных. Создал просту
базу данных из двух таблиц и написал для нее программку, которая заполнила её данными. 
Первая вот такая: 

CREATE TABLE Cities
(
    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY, 
    Name nvarchar(50) NULL
)


Она содержит 100 строк.
Вторая, главная: 

CREATE TABLE People
(
    Id int IDENTITY(1,1) NOT NULL,
    CityId int NOT NULL,
    Name nvarchar(50) NULL,
    LastName nvarchar(50) NULL,
    CONSTRAINT People_Id_Idx PRIMARY KEY CLUSTERED (Id ASC)
)

ALTER TABLE People  WITH CHECK ADD  CONSTRAINT fk_cities 
FOREIGN KEY(CityId) REFERENCES Cities (Id)


Она содержит 100 миллионов строк. Люди распределены по городам равномерно. 
Так вот вопрос. Когда я пишу очень простой запрос:

SELECT COUNT(*) -- COUNT(Id) даёт такой же результат
FROM People 


то он отрабатывает очень долго (около 100-130 секунд). Я конечно понимаю, что 10
миллионов записей - это довольно много, но всё-таки полторы-две минуты на такой запро
это очень много. Тем более, что необходимость узнать количество записей в таблице возникает довольно часто. Тем более, что сто миллионов записей - это совсем не предел. Ведь какие-нибудь банки ворочают таблицами с куда большим числом записей и столбцов. Как вообще они борются за производительность? 

Дополнительный данные: 


Версия sql-сервер: 
Microsoft SQL Server 2014 Enterprise Edition (64-bit)
План запроса: 


    

Ответы

Ответ 1

Ок, сначала стоит выяснить почему именно тормозит ваш запрос.

Я вбил в таблицу 100 млн записей. Запустил count с измерением времени (с отключенным Show Actual Execution Plan - эта опция влияет на результаты измерений!):

set statistics io on
set statistics time on
select count(*) from People

Table 'People'. Scan count 9, logical reads 942693, physical reads 0, 
   read-ahead reads 18, lob logical reads 0, lob physical reads 0, 
   lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 8064 ms,  elapsed time = 1169 ms.


Запрос выполнился чуть больше чем за секунду. План выполнения, если его просмотреть, совпадает с вашим:



Что плохо в таком плане выполнения, и почему запрос отработал быстро на моей машине, но медленно - на вашей?

В вашей таблице есть кластерный индекс (это хорошо). Что такое кластерный индекс
Это большое дерево поиска, состоящие из 8-килобайтных страниц (BTree, прошитое по уровням), в листьях которого хранятся данные записей.

Как SQL сервер считает count(*) без условия? Он считает количество записей в Primar
Key. Делает он это, проходя по нижнему уровню (листьям) дерева и подсчитывая количество записей в каждой странице. 

Каждое обращение к странице, которая уже находится в памяти - это logical read. Ка
видите, SQL Server-у пришлось перебрать 942693*8 = примерно 7.5Gb данных ради подсчет
количества строк. Это не сверхстрашно, но только в случае, когда данные таблицы уже оказались в памяти. В моем случае они лежали в памяти только потому, что SQL Server только что вставил данные в эту таблицу, и решил пока не сбрасывать кэш.

Что происходит, если страницы, из которой нужно прочитать, в памяти не оказывается
Например, когда у вас просто мало оперативной памяти, и база не влазит в ее целиком. Тогда logical read превращается в physical read - реальное чтение с диска. И вы получаете:

Table 'People'. Scan count 9, logical reads 942693, physical reads 942693...

SQL Server Execution Times:
   CPU time = ??? ms,  elapsed time = 100000 ms (условно)

Потому что SQL Server пытается читать с диска в 4/8/?? потоков, а даже линейное чтени
7.5Gb с диска по 100Mb/s дает вам задержку в 76.8 секунд (судя по скорости чтения - у вас HDD, а не SSD). Потери на нелинейность и одновременность (HDD это очень не любят) - и вот ваши 130 секунд в результате.

Что можно сделать:


Поставить больше памяти (ее никогда не бывает много!)
Поставить SSD (значительно смягчит проблему)
Заставить SQL Server читать меньше данных при подсчете count. Например, создать дополнительный некластерный индекс, в котором будут только ID:

CREATE NONCLUSTERED INDEX [People_Id_Only] ON [dbo].[People]
(
    [Id] ASC
)


... и SQL Server начнет использовать его для подсчета количества строк:

Table 'People'. Scan count 9, logical reads 125415, 


Всего прочитанных 979 мегабайт на 10 млн записей - не так уж и плохо!
Использовать материализованные представления (Indexed Views) - оптимальный вариант. Хорошо расписан в ответе от i-one, я бы предложил вам выбрать именно это решение.


Стоит отметить, что решения с индексами (3 и 4) не бесплатны с точки зрения поддержки
Индексы требуют расходов на хранение (особенно видно в (3)) и на поддержание их в актульно
состоянии (что чуть-чуть увеличивает время вставки записи). Решение на indexed view так же заметно усложняет план вставки каждой записи. Не удивляйтесь, если обнаружите что простая вставка 

insert into People (CityId, Name, LastName)
values (1, 'test', 'one')


начнет выполняться вот так:



Процентам в плане слишком сильно верить нельзя, но стоит обращать на них внимание. Хорошо видно что решения (3) и (4) совсем не бесплатны.



Кроме того, индексы обычно решают проблемы конкретных запросов. Например, созданны
в (3) индекс будет неприменим при группировке по городам. А индекс с группировкой п
городам может оказаться неэффективен при группировке по другому полю. Иногда наличие индекса может значительно ухудшить результат (например, в случае update на базе сложной выборке).

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


Ответ 2

Если COUNT нужен в целях оценки, то, как верно отметили в комментариях, проще всего обратиться к DMV. Например к sys.dm_db_partition_stats:

select sum(row_count)
from sys.dm_db_partition_stats
where object_id = object_id('People') and index_id in (0, 1);


это даст приблизительное число строк в таблице (иногда точное, но точность не гарантируется).

Если нужно точное число строк в таблице, то придётся честно выполнить запрос SELEC
COUNT(*) FROM People. В ответе участника PashaPash хорошо описаны причины, по которы
такой запрос может быть медленным и способы устранения. Мне также нравится ответ участника MaxU. Я бы использовал этот подход, только оформил бы это в виде материализованного представления:

create view mvPeopleCityCnt
with schemabinding
as
    select CityId, Cnt = count_big(*)
    from dbo.People
    group by CityId;
GO
create unique clustered index mvPeopleCityCnt_Pk on mvPeopleCityCnt (CityId);
GO


Отмечу, что можно было бы материализовать запрос select count_big(*) from Peopl
(не делая группировку по CityId). Учитывая, однако, что число городов относительно небольшое, материализация с группировкой по CityId может быть выгоднее, т.к. в этом случае в дополнение к запросу

select count(*)
from People;


обращением к индексу mvPeopleCityCnt_Pk также могут быть удовлетворены, например, и такие запросы:

select CityId, count(*)
from People
group by CityId;

select distinct CityId
from People;


При их выполнении в случае наличия материализованного представления, SqlServer обратится к нему, а не к таблице People.

Ниже сравнение производительности запроса SELECT COUNT(*) FROM People для четырёх разных подходов:

clustered index (Id):
Table 'People'. Scan count 5, logical reads 1290799 ...
CPU time = 7333 ms,  elapsed time = 2051 ms.

nonclustered index (Id):
Table 'People'. Scan count 5, logical reads 124188 ...
CPU time = 6911 ms,  elapsed time = 1840 ms.

nonclustered columnstored index (Id):
Table 'People'. Scan count 4, ..., lob logical reads 116054 ...
CPU time = 999 ms,  elapsed time = 252 ms.

materialized view:
Table 'mvPeopleCityCnt'. Scan count 1, logical reads 2 ...
CPU time = 0 ms,  elapsed time = 0 ms.


Ответ 3

Если надо быстро посмотреть количество строк в таблице, то можно воспользоватьс
хранимкой sp_spaceused

EXEC sys.sp_spaceused @objname = N'People'


Второй столбец в результате - "rows" - и будет количеством строк в таблице на текущий момент.


Ответ 4

Я думаю в данном случае надо каким-то образом кешировать уже агрерированные данные.

Один из вариантов создать таблицу:

CREATE TABLE CityStats(
    CityId      int,
    Population  int,
    CONSTRAINT CityStats_CityId_Idx PRIMARY KEY CLUSTERED (CityId ASC)
);


и обновлять (поддерживать) ее тригером на People...


Ответ 5

Узнавать число строк в таблице не имеет смысла - это число может устареть в следующи
же момент. Если только вы не повесите блокировку на всю таблицу целиком.  Если у вас такая необходимость возникает довольно часто - вы что-то делаете не так.

Для диагностики можете взять какое-нибудь приближенное решение отсюда: https://habrahabr.ru/post/271797/

А если по какой-то причине нужны точные данные, в любой момент и быстро - можно воспользоваться индексированным представлением:

create view PeopleCount with schemabinding 
as select count_big(*) as [count] from dbo.People
go

create unique clustered index PK_PeopleCount ON PeopleCount([count])


Использование:


на младших редакциях SQL Server: select [count] from PeopleCount with (noexpand)
на старших редакциях SQL Server можно сделать и так: select count(*) from People


Ответ 6

Количество строк можно узнать из системного представления dm_db_partition_stats по кластерному индексу:

SELECT SUM(s.row_count)
FROM sys.dm_db_partition_stats s
WHERE s.[object_id] = OBJECT_ID('схема.таблица')
    AND s.index_id < 2;


Также если неожиданно стал тормозить запрос типа:

SELECT COUNT(*)
FROM схема.таблица;


, то возможно стоит обновить метаданные по этой таблице с помощью команды UPDATEUSAGE:

DBCC UPDATEUSAGE(БД, 'схема.таблица') WITH COUNT_ROWS;


Более детально обсуждалось здесь


Ответ 7

Попробуй сделать индекс по People.ID

Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,


У таблицы People нет первичного ключа и поиск ведётся перебором всех записей.

Детально проблему покажет 

explain select count(*) from people;

Помогите пожалуйста с запросом SQL (Oracle). Having, Group by, агрегатная функция от агрегатной функции

Задание - вывести фамилию-имя начальника отдела и название отдела, в котором сотрудники выполнили максимальное количество проектов. У меня получился такой запрос, но выводит он не совсем то, что нужно. Сделал его по примеру отсюда (второй пункт).

SELECT department_name, max_amount FROM ( SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X INNER JOIN ( SELECT MAX(projects_amount) AS max_amount FROM ( SELECT dep_name AS department_name, COUNT(rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X ) Y ON projects_amount = max_amount
Вывод, при том, что проекта всего три:
В чем проблема - я думаю, вы поймёте, если посмотрете на схему БД. Каждому отделу должно соответствовать несколько проектов, но отделы и проекты связаны через таблицу сотрудников - и поэтому в моём запросе каждому отделу соответствует больше записей, чем нужно. Группирую по названию отдела, а получается, что в каждой группе - сотрудники этого отдела. Как связать таблицы иначе или сгруппировать иначе - я не придумал, в ступоре нахожусь. Если зайти с другой стороны, сгруппировать по id проекта - в каждой группе будет количество сотрудников, которое работало над проектом, тоже не то. Нужно решение задачи именно по этой схеме. Я знаю, что можно упростить, но задание учебное. Скрипт создания БД: http://pastebin.com/hnHnENpX

Заранее спасибо.

Ответ

SELECT emp_first_name,emp_last_name,department_name, max_amount FROM ( SELECT dep_name AS department_name,e2.emp_first_name,e2.emp_last_name, COUNT(distinct rel_prj_id) AS projects_amount FROM employees e1 INNER JOIN departments ON e1.emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = e1.emp_id INNER JOIN employees e2 ON e2.emp_id=dep_manager_id GROUP BY dep_name,e2.emp_first_name,e2.emp_last_name ) X INNER JOIN ( SELECT MAX(projects_amount) AS max_amount FROM ( SELECT dep_name AS department_name, COUNT(distinct rel_prj_id) AS projects_amount FROM employees INNER JOIN departments ON emp_dep_id = dep_id INNER JOIN rel_prj_emp ON rel_emp_id = emp_id GROUP BY dep_name ) X ) Y ON projects_amount = max_amount
Хотя верхний подзапрос немного странно смотрится, логичнее бы выглядело, если бы отделы были первой таблицей и к ней все клеилось. Хотя в данном случае порядок ни на что не влияет (т.к. в inner join таблицы слева и справа равнозначны).

Запрос в MySQL - найти числа вне интервалов

Пишу выборку и наверное торможу. Первая таблица - набор от 1 до 1000, например A=1 A=2 A=3 ... A=999 A=1000
Вторая таблица - набор пар значений, интервалы, например, A=100 B=150 А=1 В=40 A=300 B=350
Сначала нарисовал поиск чисел из первой таблицы, которых нет во второй (т.е. по полю А) :
SELECT t1.a FROM table1 t1 left join table2 t2 on (t1.a=t2.a) WHERE t2.a IS NULL
Работает! Теперь ищу числа из первой таблицы, которые не попадают в ИНТЕРВАЛЫ второй таблицы и тут встал... Еще один джойн что ли делать? А в итоге надо найти одно минимальное значение из выборки, т.е. в данном случае это 41.

Ответ

Так?
select min(t1.a) a from table1 t1 left join (select distinct tt1.a from table1 tt1 join table2 tt2 on tt1.a between tt2.a and tt2.b ) tt on t1.a=tt.a where tt.a is null

Передать значение выбранного select в onclick

Подскажите пожалуйста, как можно передать значение выбранного select в onclick.
Вот мой кусок кода, он рабочий:
ссылка
Мне нужно подставлять цену в пункт amount из select, но как это сделать даже в голову не приходит и можно ли так вообще?) Заранее спасибо!

Ответ

amount: document.getElementById('select id').value,

Оптимизация SELECT со статистикой

Есть такой запрос:
SELECT COUNT(`count`) AS 'visits', `code` FROM `om_log` WHERE `code` <> '0' AND `date` >='1464728400' AND `date` <='1467320399' GROUP BY `code` ORDER BY `code`;
В таблице записей очень много, это статистика посещения сайта. Обычно она собирается за месяц. Есть идеи как оптимизировать данный запрос, не прибегая к рефакторингу и использованию промежуточных таблиц с счетчиками?
EXPLAIN:
id | select_type | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | range | "code,date" | date | 5 | NULL | 1420 | "Using where; Using temporary; Using filesort"

Ответ

Данную оптимизацию может зделать MySQL вместо меня с помощью механизма партиционирования. Это физическое разделение файла таблицы на несколько по определенному признаку. Таким образом запросы начинают выполнятся в разы быстрее. Главное не переусердствовать с их количеством.
ALTER TABLE `om_log` PARTITION BY RANGE(date) PARTITIONS 6( PARTITION less2015 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01')), PARTITION less2016 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01')), PARTITION less2017 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01')), PARTITION less2018 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01')), PARTITION less2019 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01')), PARTITION other VALUES LESS THAN (MAXVALUE) );
Сейчас я сделал их с запасом, но похорошому не плохо добавлять партиции кроном при необходимости, например в конце года, а партицию 5летней давности допустим удалять.
Сделать партиционирование существующей таблицы, которая активно используется невозможно, возникает блокировка таблицы и это ложит сайт. Необходимо создавать новую таблицу, на которую переключать работу сайта, а после переносить небольшими порциями все записи туда.
Сейчас решена задача добавлениям комбинированных индексов! Скорость запросов 2-5 сек. Это более менее приемлемое время вместо бывших 90сек.

Несколько результатов из БД, с учётом сортировки

Имеется таблица book в которой около 50 записей. Поля таблицы id(int),name(varchar),path(text),lang(int, value = 1,2,3). lang=1 примерно 18 записей lang=2 примерно 16 записей lang=3 примерно 16 записей Необходимо составить sql запрос, чтобы выбрать 3 книги, первая из которых это случайное число из первых 18 записей, второе это тоже случайное число из вторых 16 записей, и третья соответственно тоже случайное число из последних 16 записей
Что то на подобие такого: SELECT * FROM book WHERE lang=1 ORDER BY RAND () LIMIT 1 AND WHERE lang=2 ORDER BY RAND () LIMIT 1 AND WHERE lang=3 ORDER BY RAND () LIMIT 1;

Ответ

Для ваших объемов данных подойдет очень простой запрос:
SELECT random_book.* FROM book LEFT JOIN (SELECT * FROM book ORDER BY RAND()) random_book ON (book.lang = random_book.lang) GROUP BY random_book.lang
ORDER BY RAND() - это не быстро на больших обьемах данных.

Есть ли разница при SELECT * или SELECT `some_column` в PHP

Интересует насколько это влияет на производительность запроса в PHP
все запросы я делаю просто SELECT * ... LIMIT 0,20 и он постоянно подтягивает значения всех столбцов (их примерно до 15), но использую из них я максимум 6-7.
У меня все время стоит лимит, а в базе строк больше > 10000 вот интересно это сильно влияет на скорость? а что если дальше продолжать еще делать JOIN'ы ?

Ответ

В большинстве случаев морочиться на эту тему не нужно. Вопрос про * в запросах - это такая trivia, которую нубы с придыханием рассказывают друг другу и которую можно прочитать в идиотских списках "25 способов ускорить свой скрипт в 100500 раз".
Если удобно писать * и в базе нет блобов с гигазами варезов, а есть 15 несчастных варчаров, то никто не умрет от звездочки.
Плюсы использования звездочки:
при добавлении полей в таблицу не надо менять код запроса запрос становится читабельнее и короче
Минусы использования звездочки:
большой потенциальный объем данных, передаваемых в пхп, может сказаться на производительности если в запросе используется временная таблица, то наличие ненужных полей может катастрофически отразиться на времени запроса, поскольку во временную таблицу будут записываться все поля таблицы. Разумеется, лучше избегать запросов, которым требуется создание временной таблицы.

Вывод значения при условии, что подобного нету в другой таблице

Нужно вывести информацию с db_order, если нету ни одного повторения значения с db_order.id в таблице db_ips.order_id
Сами таблицы:
db_ips
db_order
Спасибо.

Ответ

Если я правильно понял ваш вопрос, то как то так:
SELECT db_order.* FROM db_order LEFT OUTER JOIN db_ips ON db_order.id = db_ips.order_id WHERE db_ips.order_id IS NULL;

Как найти незанятые области через запрос MySql

Приложил картинку: в базе хранятся закрашенные области (серый цвет), как координаты левого верхнего угла и правого нижнего, т.е. ЧЕТЫРЕ числа (х1,у1,х2,у2). Как запросом в базу MySql получить координаты свободной области, если условия такие:
начиная сверху слева найти свободную область размером 1х1 начиная сверху слева найти свободную область размером 2х2
То есть для п.1 ответ должен быть 0,0,1,1 (голубой квадратик) для п.2 = 6,0,8,2 (оранжевый квадратик)
Все, на что хватило знаний)) -это функцией MIN нахожу свободную координату по оси X, но в совокупности с осью Y это неправильно. И для поиска областей 2х2 поиск через MIN вообще не применим. Как вариант, думал создать еще одну таблицу с перечислением всех возможных точек (т.е. для данного квадрата это 10х10=100 строк) и ставить пометку - такие-то точки заняты, а такие-то нет и искать среди незанятых, но если размер поля надо будет увеличить (с 10х10 до например 100х100), то скорее всего начнутся задержки??? База:
DROP TABLE IF EXISTS `m_items`; CREATE TABLE `m_items` ( `id` int(11) NOT NULL, `x1` smallint(4) NOT NULL, `y1` smallint(4) NOT NULL, `x2` smallint(4) NOT NULL, `y2` smallint(4) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `m_items` (`id`, `x1`, `y1`, `x2`, `y2`) VALUES (1, 3, 0, 6, 4), (2, 8, 0, 9, 2), (3, 0, 1, 2, 2), (4, 0, 3, 2, 5), (5, 7, 3, 9, 7), (6, 1, 6, 6, 7), (7, 1, 8, 2, 10);

Ответ

Попробуйте так:
select x,y from c -- см. ответ @mnv left join m_items on x <= X2-1 and x+2 >= X1 and y <= Y2-1 and y+2 >= Y1 where x+2<10 and y+2<10 and id is null order by y, x;
+2 - это квадраты 3x3.
SQLFiddle