индексы в mysql SELECT AS или с использованием представлений

Я над головой с большим запросом mysql (mysql 5.0), и я надеюсь, что кто-то здесь может помочь.

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

Ответ, который я получил (используя подзапрос с соединением как)

select *
from media m
inner join
     ( select uid
     from users_tbl
     limit 0,30) map
  on map.uid = m.uid
inner join users_tbl u
  on u.uid = m.uid

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

мой запрос теперь выглядит так

SELECT mdate.bid, mdate.fid, mdate.date, mdate.time, mdate.title, mdate.name, 
       mdate.address, mdate.rank, mdate.city, mdate.state, mdate.lat, mdate.`long`,
       ext.link, 
       ext.source, ext.pre, meta, mdate.img
FROM ext
RIGHT OUTER JOIN (
  SELECT media.bid, 
         media.date, media.time, media.title, users.name, users.img, users.rank, media.address, 
         media.city, media.state, media.lat, media.`long`,
         GROUP_CONCAT(tags.tagname SEPARATOR ' | ') AS meta
  FROM media
  JOIN users ON media.bid = users.bid
  LEFT JOIN tags ON users.bid=tags.bid
  WHERE `long` BETWEEN -122.52224684058 AND -121.79760915942
    AND lat BETWEEN 37.07500915942 AND 37.79964684058
    AND date = '2009-02-23'
  GROUP BY media.bid, media.date
  ORDER BY media.date, users.rank DESC
  LIMIT 0, 30
) mdate ON (mdate.bid = ext.bid AND mdate.date = ext.date)

фу!

Итак, как видите, если я правильно понимаю свою проблему, у меня есть две производные таблицы без индексов (и я не отрицаю, что, возможно, я как-то напортачил с операторами Join, но я продолжал возиться с разными типами, это закончилось дал мне результат, который я хотел).

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

В настоящее время мой запрос выполняется за 0,8 секунды, но я уверен, что если бы я мог воспользоваться преимуществами индексов, это могло бы быть значительно быстрее.


mysql indexing derived-table
person pedalpete    schedule 23.02.2009    source источник
comment
Изучив рекомендацию Quassnoi по использованию SPACIAL INDEX, я понял, что это была ошибка в индексе, который у меня был, и время отклика значительно сократилось (теперь 0,1 секунды). Однако EXPLAIN по-прежнему указывает, что я запрашиваю неиндексированные столбцы.   -  person pedalpete    schedule 24.02.2009

Ответы (3)


arrow_upward
1
arrow_downward

Во-первых, проверьте индексы на ext(bid, date), users(bid) и tags(bid), они у вас действительно должны быть.

Однако кажется, что именно LONG и LAT вызывают у вас больше всего проблем. Вы должны попытаться сохранить свои LONG и LAT как (coordinate POINT ), создайте SPATIAL INDEX в этом столбце и выполните такой запрос:

WHERE MBRContains(@MySquare, coordinate)

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

CREATE INDEX ix_date_long ON media (date, `long`)
CREATE INDEX ix_date_lat ON media (date, lat)

Эти индексы будут более эффективными для вашего запроса, поскольку вы используете точный поиск по date в сочетании с ранжированным поиском по axes.

person Quassnoi    schedule 23.02.2009
comment
Хотя я не отрицаю преимущества ПРОСТРАНСТВЕННОГО ИНДЕКСА, по какой-то причине мне не удалось создать его в своей базе данных (вероятно, потому, что я храню широту и долготу отдельно. Мне придется изучить это. Однако в процессе я понял, что у меня была ошибка в моем индексе latlong.Время запроса теперь составляет 0,1 сек. - person pedalpete; 24.02.2009
comment
Если вам нужен ПРОСТРАНСТВЕННЫЙ, вы должны сохранить свои координаты как особый тип, ГЕОМЕТРИЯ (ТОЧКА, в вашем случае). И тот факт, что индекс LAT/LONG действительно помог, говорит нам, что вам действительно нужен SPATIAL :) - person Quassnoi; 24.02.2009

arrow_upward
1
arrow_downward

Начиная с чистого листа:

Вопрос: почему вы группируете по media.bid и media.date? Может ли ставка содержать записи более чем на одну дату?

Вот более простая версия, чтобы попробовать:

SELECT 
    mdate.bid,
    mdate.fid,
    mdate.date,
    mdate.time,
    mdate.title,
    mdate.name, 
    mdate.address,
    mdate.rank,
    mdate.city,
    mdate.state,
    mdate.lat,
    mdate.`long`,
    ext.link, 
    ext.source,
    ext.pre, 
    meta,
    mdate.img,
    (   SELECT GROUP_CONCAT(tags.tagname SEPARATOR ' | ')
        FROM tags 
        WHERE ext.bid = tags.bid
        ORDER BY tags.bid GROUP BY tags.bid
    ) AS meta

FROM 
    ext

LEFT JOIN
    media ON ext.bid = media.bid AND ext.date = media.date

JOIN
    users ON ext.bid = users.bid

WHERE 
    `long` BETWEEN -122.52224684058 AND -121.79760915942
    AND lat BETWEEN 37.07500915942 AND 37.79964684058
    AND ext.date = '2009-02-23'
    AND users.userid IN
    (    
        SELECT userid FROM users ORDER BY rank DESC LIMIT 30
    )

ORDER BY 
    media.date, 
    users.rank DESC
    LIMIT 0, 30
person dkretz    schedule 23.02.2009
comment
Ну я ни по чему не группирую, просто отформатировал запрос так, чтобы он был читабелен :) - person Quassnoi; 24.02.2009
comment
спасибо, le dorfier, теперь у меня все работает довольно хорошо, но когда я закончу свой текущий крайний срок, я попытаюсь переписать запрос по-вашему и посмотреть, смогу ли я заставить его работать. Он выглядит проще. - person pedalpete; 26.02.2009

arrow_upward
0
arrow_downward

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

создать таблицу #что угодно создать таблицу #что угодно2

вставить в #что-либо выбрать... вставить в #что-либо2 выбрать...

выбрать из #что угодно присоединиться #что угодно 2

....

drop table #что угодно drop table #whatever2

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

person Myforwik    schedule 24.02.2009