/*
A namemap: genre, filename, info, size, info_hash, date, description, image
B users: uploader
C categories: category
D summary: seeds, leechers, finished, dlbytes, speed
E peers: sumbytes, totalpeers
F ratings: totrate
*/
$arr = $db->getAll("
SELECT a.genre, a.filename, a.info, a.size, a.info_hash, a.data as date, a.image, b.username as uploader, c.name as category, d.seeds, d.leechers, d.finished, d.dlbytes, d.speed, ((((a.size * count(e.pid)) - sum(e.bytes)) / (a.size * count(e.pid))) * 100) as prgsf, (sum(f.rating) / count(f.added)) as totrate
FROM namemap a LEFT JOIN users b ON (a.uploader = b.id) LEFT JOIN categories c ON (c.id = a.category) LEFT JOIN summary d ON (d.info_hash = a.info_hash) LEFT JOIN peers e ON (e.infohash = a.info_hash) LEFT JOIN ratings f ON (f.infohash = a.info_hash)
GROUP BY a.genre, a.filename, a.info, a.size, a.info_hash, a.data, a.image, b.username, c.name, d.seeds, d.leechers, d.finished, d.dlbytes, d.speed
ORDER BY data DESC
LIMIT 10;
");
select count, sum, .. left join, group by с тормозами в одном запросе.
Такая вот проблемка. Составил sql запрос, и как оказалось занимает в среднем 7 секунд. Хотелось бы сократить затрачиваемое время на выборку из базы, таким образом что бы не посылать более двух запросов для получения данных.
Код:
Какие индексы стоят в таблицах? Какая БД? Сколько всего данных в таблицах(примерно)?
по полям какого типа идет обїединение?
Цитата: shine
Какие индексы стоят в таблицах? Какая БД? Сколько всего данных в таблицах(примерно)?
Индексы:
Код:
SHOW INDEX FROM namemap;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| namemap | 0 | PRIMARY | 1 | info_hash | A | 5708 | NULL | NULL | | BTREE | |
| namemap | 1 | filename | 1 | filename | A | 5708 | NULL | NULL | YES | BTREE | |
| namemap | 1 | category | 1 | category | A | 50 | NULL | NULL | | BTREE | |
| namemap | 1 | uploader | 1 | uploader | A | 380 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users | 0 | PRIMARY | 1 | id | A | 5716 | NULL | NULL | | BTREE | |
| users | 0 | username | 1 | username | A | 5716 | NULL | NULL | | BTREE | |
| users | 1 | id_level | 1 | id_level | A | 8 | NULL | NULL | | BTREE | |
| users | 1 | pid | 1 | pid | A | 5716 | NULL | NULL | | BTREE | |
| users | 1 | cip | 1 | cip | A | 5716 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM categories;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| categories | 0 | PRIMARY | 1 | id | A | 48 | NULL | NULL | | BTREE | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM summary;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| summary | 0 | PRIMARY | 1 | info_hash | A | 5709 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM peers;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| peers | 0 | PRIMARY | 1 | infohash | A | 4882 | NULL | NULL | | BTREE | |
| peers | 0 | PRIMARY | 2 | peer_id | A | 29297 | NULL | NULL | | BTREE | |
| peers | 0 | sequence | 1 | sequence | A | 29297 | NULL | NULL | | BTREE | |
| peers | 1 | pid | 1 | pid | A | 1395 | NULL | NULL | YES | BTREE | |
| peers | 1 | ip | 1 | ip | A | 1395 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM ratings;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| ratings | 1 | infohash | 1 | infohash | A | 4668 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| namemap | 0 | PRIMARY | 1 | info_hash | A | 5708 | NULL | NULL | | BTREE | |
| namemap | 1 | filename | 1 | filename | A | 5708 | NULL | NULL | YES | BTREE | |
| namemap | 1 | category | 1 | category | A | 50 | NULL | NULL | | BTREE | |
| namemap | 1 | uploader | 1 | uploader | A | 380 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users | 0 | PRIMARY | 1 | id | A | 5716 | NULL | NULL | | BTREE | |
| users | 0 | username | 1 | username | A | 5716 | NULL | NULL | | BTREE | |
| users | 1 | id_level | 1 | id_level | A | 8 | NULL | NULL | | BTREE | |
| users | 1 | pid | 1 | pid | A | 5716 | NULL | NULL | | BTREE | |
| users | 1 | cip | 1 | cip | A | 5716 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM categories;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| categories | 0 | PRIMARY | 1 | id | A | 48 | NULL | NULL | | BTREE | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM summary;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| summary | 0 | PRIMARY | 1 | info_hash | A | 5709 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM peers;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| peers | 0 | PRIMARY | 1 | infohash | A | 4882 | NULL | NULL | | BTREE | |
| peers | 0 | PRIMARY | 2 | peer_id | A | 29297 | NULL | NULL | | BTREE | |
| peers | 0 | sequence | 1 | sequence | A | 29297 | NULL | NULL | | BTREE | |
| peers | 1 | pid | 1 | pid | A | 1395 | NULL | NULL | YES | BTREE | |
| peers | 1 | ip | 1 | ip | A | 1395 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
SHOW INDEX FROM ratings;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| ratings | 1 | infohash | 1 | infohash | A | 4668 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Версия:
Код:
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.45 |
+-----------+
+-----------+
| VERSION() |
+-----------+
| 5.0.45 |
+-----------+
Всего данных в Mb:
Код:
$ mysql -u <user> --password=<pass> <db_name> -e 'show table status;' | awk '{sum=sum+$7+$9;} END {print sum/1024/1024}'
223,317
223,317
Цитата: squirL
по полям какого типа идет обїединение?
int, bigint и varchar 255 вроде.