» Оптимизация запроса MySQL

Подскажите, пожалуйста, как эти два запроса преобразовать в один ?

Код:
$arr_t = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) AS cnt FROM users WHERE userid=$userid"));
$arr_s = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) AS cnt2 FROM users WHERE userid=$userid AND isreader='yes'"));
$first = $arr_t["cnt"];
$second = $arr_s["cnt2"];

ROMAHi4
http://dev.mysql.com/doc/refman/5.0/en/union.html

Так случилось, что для меня очень остро встал вопрос оптимизации обращений к базе данных. Оптимизацию начал изучать с нуля. Когда изучаешь, легче идёт, когда задаёшь вопросы по ходу. Вот первый из них -
Получается, что в MySQL задействовано индексирование всегда, даже, если не используется директива USE UNDEX в запросе?

не индексирование а индексы наверное? если есть эти самые индексы и нет директивы IGNORE INDEX (...) то да

Следовательно, мой шанс найти зависающий запрос - это проанализировать эти индексы, и задействовать их по свойму, более рационально....
И для этого - команды SHOW INDEXES и EXPLAIN мне в руки....
Вроде так.... Пока, правильно понимаю политику партии?


Добавлено:
Стоп. Фразу :

Цитата:

если есть эти самые индексы

не понял.

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

http://www.mysql.ru/docs/man/MySQL_indexes.html
http://www.kurepin.ru/php/index/
http://www.instanceof.ru/mysql/indexes-in-mysql

andead! Класс! Я тоже юзал Гугл, но таких классных статей не нашёл. Огромный сенкс!

Добавлено:
"Однако в случае, когда необходим доступ почти ко всем 1000 строкам, быстрее будет последовательное чтение, так как при этом не требуется операций поиска по диску. "
Принципиальнейший момент! Причём, я это уже когда то читал. И потом благополучно забыл...

Добавлено:
"Как только к вашей базе обратятся сотни человек, так ваши тысячи записей тут же превратятся для движка баз данных в миллионы! И ваш провайдер совершенно резонно сделает вам замечание." - Это именно то, что со мной и произошло. Только это не провайдер обратился, а сайт стал падать, показывая "Сервер Ерроу".

Добавлено:
Лыжи не едут.
Почему строка -
$result = mysql_query("EXPLAIN SELECT * FROM `tbl_photos`") or die("Invalid query: " . mysql_error());
Работает как обычный запрос? Я же написал волшебное слово "EXPLAIN", как учили!

в phpMyAdmin забейте этот запрос

Да, вывело. Теперь ещё надо разобраться в том, что вывело.
То есть, получается, директива "EXPLAIN" работает в общем случае только из под Shell?
Из под РНР скриптов это не запуститься?

должно и из под php запускаться

Тогда вообще ни чего не понял.... Как же мне запустить из под РНР то, что у меня сработало из под phpMyAdmin -
EXPLAIN SELECT * FROM `tbl_photos

Код: $result = mysql_query("EXPLAIN SELECT * FROM tbl_photos);
print_r(mysql_fetch_assoc($result));

Да, спасибо большое, теперь всё вывелось.

Цитата:

Как только к вашей базе обратятся сотни человек, так ваши тысячи записей тут же превратятся для движка баз данных в миллионы!

какой волшебный и глупый вывод.

Код: EXPLAIN SELECT pto.* , photogr.id_imageaccess FROM tbl_photos AS `pto` , tbl_photographers as `photogr` WHERE ( pto.keywords LIKE '%tel%' OR pto.location LIKE '%tel%' OR pto.photo_id LIKE '%tel%' ) AND pto.photographer_id = photogr.photographer_id AND pto.status = 1 ORDER BY pto.rating DESC

israel_rider
если по этим полям осуществляется полнотекстовый поиск, то не нужно ничего удалять

andead! Плиз! Только на Вас вся надежда! Вроде нашёл узкое место.... Запрос :

Код: EXPLAIN SELECT photo_id, SUM(watch_count) AS `total_watch_count` FROM `tbl_viewed` WHERE photo_id IN (SELECT photo_id FROM tbl_photos WHERE status = 1) AND `timeviewed` > 1225353785 GROUP BY `photo_id` ORDER BY `total_watch_count` DESC LIMIT 0 , 10

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

Код: EXPLAIN SELECT photo_id, SUM(watch_count) AS `total_watch_count` FROM `tbl_viewed`
LEFT JOIN tbl_photos ON tbl_viewed.photo_id = tbl_photos.photo_id
WHERE
    tbl_photos.status = 1 AND
    `timeviewed` > 1225353785
GROUP BY `photo_id`
ORDER BY `total_watch_count` DESC LIMIT 0, 10

Да, сделал. Только поменял
SELECT photo_id, SUM(watch_count) AS `total_watch_count` FROM `tbl_viewed`
на
SELECT tbl_viewed.photo_id, SUM( watch_count ) AS `total_watch_count`
Сейчас думаю.... по логике скорее должен стоять райт джойнт, так что в результате:

Код:
SELECT tbl_viewed.photo_id, SUM( watch_count ) AS `total_watch_count`
FROM `tbl_viewed`
RIGHT JOIN tbl_photos ON tbl_viewed.photo_id = tbl_photos.photo_id
WHERE tbl_photos.status =1
AND `timeviewed` >1225353785
GROUP BY `photo_id`
ORDER BY `total_watch_count` DESC
LIMIT 0 , 10

Цитата:

Что скажете?

ничего хорошего) Using filesort это худший вариант

неплоха бы замерить время выполнения первого и второго запроса

andead, дорогой! Вы не представляете, как я Вам благодарен!!!! Сначала я зациклился на "Using filesort", читал статьи, плясал с бубном.... В голове у меня окончательно всё перемешалось. Дело в том, что програмирование я учил очень быстро, так сложились обстаятельства. И целые области оказались пропущенными. Опримизация запросов к базе данных - вообще область новая для меня. И вдруг свалилось! И надо было срочно сделать, сайт висел, клиент бился головой об стену. Но вопрос то не простой. Не возможно в последнюю ночь перед экзаменом выучить то, что пропускал в течении всей сессии.
Запросы, как Вы видите, у меня сложные. Отделаться от "Using filesort" при наличии и "GROUP BY" и "ORDER BY " практически невозможно....
И вот, совсем было отчаявшись, и вспомнил про Ваш совет посмотреть время выполнения запроса в том и в другом случае. И просто о....л!!!! Если в первом случае время составило 7!!!!!! секунд, то во втором случае оно составило 0.8 секунд!!!! Я быстренько всё поправил. Клиент вернулся к жизни .
Короче, если только Вы и уважаемый Cheery будете в наших краях, с меня причитается!

всегда пожалуйста) на самом деле 0,8 секунд это всё равно очень многа для sql запроса, нужно либо менять метод сбора статистики о просмотрах, либо прикручивать кеширование

Попробовал последовать совету andead. Переделал запрос, так, что бы обходиться обращением только к одной таблице. Но GROUP BY и ORDER BY всё равно остались… И видимо как результат – «Using filesort» и время – 0.4 секунды.
Вот таблица с отчётом –
http://rapidshare.com/files/300112681/__________.htm.html
Вопрос – что делать, что бы время сократилось до приемлемого? Придумывать составной индекс?

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

Код: foreach ($BindArray as $val){
$keywords_search = explode(",", $val['keywords']);
$match = array_intersect($keywords_search, $keywords_pattern);
if(count($match) > 6) {
$SQLUPDATE = "UPDATE `tbl_photos` SET `similar_pictures`=".count
WHERE `photo_id` = '".$val['photo_id']."'";
$this->tep_mysql_query($SQLUPDATE);
}
}

не легче ли вам пропустить скрипт через профайлер и посмотреть узкие места, чем тыкать пальцем в небо? хороший профайлер есть в NuSphere PhpED

Понял. Давно уже об этом думал. Но не знал, как приступить. Спасибо, буду пробовать.

Пипл, помогите!
На компе локально под ХРю стоит Апач, РНР, MySQL .
Включил опцию Slow Query Log. Включилось всё вроде корректно, судя по переменным, список переменных привожу –
[more]
abort-slave-event-count 0
allow-suspicious-udfs FALSE
auto-increment-increment 1
auto-increment-offset 1
automatic-sp-privileges TRUE
back_log 50
basedir C:/MySQL Server 5.1/
bind-address (No default value)
binlog-row-event-max-size 1024
binlog_cache_size 32768
binlog_format (No default value)
bulk_insert_buffer_size 8388608
character-set-client-handshake TRUE
character-set-filesystem binary
character-set-server utf8
character-sets-dir C:\MySQL Server 5.1\share\charsets\
chroot (No default value)
collation-server utf8_general_ci
completion-type 0
concurrent-insert 1
connect_timeout 10
console FALSE
datadir .
datetime_format %Y-%m-%d %H:%i:%s
date_format %Y-%m-%d
default-character-set utf8
default-collation utf8_general_ci
default-storage-engine INNODB
default-table-type INNODB
default-time-zone (No default value)
default_week_format 0
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
des-key-file (No default value)
disconnect-slave-event-count 0
div_precision_increment 4
enable-locking FALSE
enable-named-pipe FALSE
engine-condition-pushdown TRUE
expire_logs_days 0
external-locking FALSE
flush_time 1800
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (No default value)
gdb FALSE
general_log FALSE
general_log_file (No default value)
group_concat_max_len 1024
help TRUE
init-connect (No default value)
init-file (No default value)
init-slave (No default value)
innodb TRUE
innodb-adaptive-hash-index TRUE
innodb-additional-mem-pool-size 2097152
innodb-autoextend-increment 8
innodb-autoinc-lock-mode 1
innodb-buffer-pool-size 8388608
innodb-checksums TRUE
innodb-commit-concurrency 0
innodb-concurrency-tickets 500
innodb-data-file-path (No default value)
innodb-data-home-dir (No default value)
innodb-doublewrite TRUE
innodb-fast-shutdown 1
innodb-file-io-threads 4
innodb-file-per-table FALSE
innodb-flush-log-at-trx-commit 1
innodb-flush-method (No default value)
innodb-force-recovery 0
innodb-lock-wait-timeout 50
innodb-locks-unsafe-for-binlog FALSE
innodb-log-buffer-size 1048576
innodb-log-file-size 10485760
innodb-log-files-in-group 2
innodb-log-group-home-dir (No default value)
innodb-max-dirty-pages-pct 90
innodb-max-purge-lag 0
innodb-mirrored-log-groups 1
innodb-open-files 300
innodb-rollback-on-timeout FALSE
innodb-stats-on-metadata TRUE
innodb-status-file FALSE
innodb-support-xa TRUE
innodb-sync-spin-loops 20
innodb-table-locks TRUE
innodb-thread-concurrency 8
innodb-thread-sleep-delay 10000
interactive_timeout 28800
join_buffer_size 131072
keep_files_on_create FALSE
key_buffer_size 8388608
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language C:\MySQL Server 5.1\share\english\
lc-time-names en_US
local-infile TRUE
log (No default value)
log-bin (No default value)
log-bin-index (No default value)
log-bin-trust-function-creators FALSE
log-bin-trust-routine-creators FALSE
log-error
log-isam myisam.log
log-output FILE
log-queries-not-using-indexes FALSE
log-short-format FALSE
log-slave-updates FALSE
log-slow-admin-statements FALSE
log-slow-slave-statements FALSE
log-tc tc.log
log-tc-size 24576
log-update (No default value)
log-warnings 1
log_slow_queries slowquery.log
long_query_time 1
low-priority-updates FALSE
lower_case_table_names 1
master-connect-retry 60
master-host (No default value)
master-info-file master.info
master-password (No default value)
master-port 3306
master-retry-count 86400
master-ssl FALSE
master-ssl-ca (No default value)
master-ssl-capath (No default value)
master-ssl-cert (No default value)
master-ssl-cipher (No default value)
master-ssl-key (No default value)
master-user test
max-binlog-dump-events 0
max_allowed_packet 1048576
max_binlog_cache_size 4294963200
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_join_size 18446744073709551615
max_length_for_sort_data 1024
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
memlock FALSE
min_examined_row_limit 0
multi_range_count 256
myisam-recover OFF
myisam_block_size 1024
myisam_data_pointer_size 6
myisam_max_extra_sort_file_size 2147483648
myisam_max_sort_file_size 107374182400
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
myisam_use_mmap FALSE
ndb-use-copying-alter-table FALSE
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new FALSE
old FALSE
old-alter-table FALSE
old-passwords FALSE
old-style-user-limits FALSE
open_files_limit 622
optimizer_prune_level 1
optimizer_search_depth 62
pid-file C:\Documents and Settings\All Users\Applicatio
n Data\MySQL\MySQL Server 5.1\Data\st1.pid
plugin-load (No default value)
plugin_dir C:\MySQL Server 5.1\lib/plugin
port 3306
port-open-timeout 0
preload_buffer_size 32768
profiling_history_size 15
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type 1
query_cache_wlock_invalidate FALSE
query_prealloc_size 8192
range_alloc_block_size 4096
read_buffer_size 65536
read_only FALSE
read_rnd_buffer_size 262144
record_buffer 65536
relay-log (No default value)
relay-log-index (No default value)
relay-log-info-file relay-log.info
relay_log_purge TRUE
relay_log_space_limit 0
replicate-same-server-id FALSE
report-host (No default value)
report-password (No default value)
report-port 3306
report-user (No default value)
rpl-recovery-rank 0
safe-user-create FALSE
secure-auth FALSE
secure-file-priv (No default value)
server-id 0
shared-memory FALSE
shared-memory-base-name MYSQL
show-slave-auth-info FALSE
skip-grant-tables FALSE
skip-slave-start FALSE
slave-exec-mode STRICT
slave-load-tmpdir C:\DOCUME~1\user\LOCALS~1\Temp
slave_compressed_protocol FALSE
slave_net_timeout 3600
slave_transaction_retries 10
slow-query-log TRUE
slow_launch_time 2
slow_query_log_file slowquery.log
socket MySQL
sort_buffer_size 217088
sporadic-binlog-dump-fail FALSE
sql-mode STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENG
INE_SUBSTITUTION
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
symbolic-links TRUE
sync-binlog 0
sync-frm TRUE
sysdate-is-now FALSE
table_cache 256
table_definition_cache 256
table_lock_wait_timeout 50
table_open_cache 256
tc-heuristic-recover (No default value)
temp-pool FALSE
thread_cache_size 8
thread_concurrency 10
thread_stack 196608
timed_mutexes FALSE
time_format %H:%i:%s
tmpdir (No default value)
tmp_table_size 5242880
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
updatable_views_with_limit 1
use-symbolic-links TRUE
verbose TRUE
wait_timeout 28800
warnings 1

To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.
091104 8:42:43 [Warning] Forcing shutdown of 1 plugins
[/more]
Запускаю страничку, где запрос гарантированно выполняется 15!!!!! секунд. Но файл «slowquery.log» на компе не нахожу!
Где же может быть ошибка???

Ошибка

1) подскажите как оптимизировать такой запрос в один запрос?

как-то через join делают... тут http://www.sql.ru/forum/actualthread.aspx?tid=678367


Код:

$c->model('DBI')->dbh->do(
'DELETE from friends
WHERE id_un = ?
AND id_un_friends = ? );

$c->model('DBI')->dbh->do(
'DELETE from friends
WHERE id_un = ?
AND id_un_friends = ? );



$c->model('DBI')->dbh->do(
'DELETE from send_friends
WHERE id_un = ?
AND id_un_friends = ? );

$c->model('DBI')->dbh->do(
'DELETE from send_friends
WHERE id_un = ?
AND id_un_friends =? );

Цитата:

$c->model('DBI')->dbh->do(
'DELETE from friends
WHERE id_un = ?
AND id_un_friends = ? );

$c->model('DBI')->dbh->do(
'DELETE from friends
WHERE id_un = ?
AND id_un_friends = ? );



$c->model('DBI')->dbh->do(
'DELETE from send_friends
WHERE id_un = ?
AND id_un_friends = ? );

$c->model('DBI')->dbh->do(
'DELETE from send_friends
WHERE id_un = ?
AND id_un_friends =? );

можно объединить и в 1 запрос.. просто нужно перечислять все условия..
DELETE from send_friends WHERE ( (id_un = ? AND id_un_friends = ?) and (id_un = ? AND id_un_friends = ?) )
но стоит ли, зависит от того, сколько запоросов на удаления