Question

这几乎促使我停下来。

我回答如下问题:

SELECT * FROM `photo_person` WHERE photo_person.photo_id IN (SELECT photo_id FROM photo_person WHERE `photo_person`.`person_id` = 1 )

When I change the id, I get different processing time. Although it s all the same queries and tables. By changing the person_id I get the following:

页: 1 彩票为0.4523 sec。

- 个人=2 彩礼为0.1340 sec。

- 个人=3人(470人) 彩票为0.0194 sec

- 个人=4 薪酬为0.0024美元。

I do not understand how with the increase of the number of records/results the query time is lower. The table structures are very straight forward

UPDATE:我已经解脱了我的奇迹,因此,每当我接受问询时,我就会得到同样的准确价值(当然,在奇洛尔,但可以忽略)

UPDATE: table is MyISAM

CREATE TABLE IF NOT EXISTS `photo_person` (
`entry_id` int(11) NOT NULL AUTO_INCREMENT,
`photo_id` int(11) NOT NULL DEFAULT  0 ,
`person_id` int(11) NOT NULL DEFAULT  0 ,
PRIMARY KEY (`entry_id`),
UNIQUE KEY `PhotoID` (`photo_id`,`person_id`),
KEY `photo_id` (`photo_id`),
KEY `person_id` (`person_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=182072 ;

此处是定性分析的结果。

+----------+------------+-----------------------------+
| Query_ID | Duration   |Query                        |
+----------+------------+-----------------------------+
|        1 | 0.45541200 | SELECT ...`person_id` = 1 ) |
|        2 | 0.44833700 | SELECT ...`person_id` = 2 ) |
|        3 | 0.45587800 | SELECT ...`person_id` = 3 ) |
|        4 | 0.45074900 | SELECT ...`person_id` = 4 ) |
+----------+------------+-----------------------------+

now since the number are the same, it must be the caching :( So the aparently the caching kicks in a certain number of records or bytes

mysql> SHOW VARIABLES LIKE "%cac%";
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| binlog_cache_size            | 32768      |
| have_query_cache             | YES        |
| key_cache_age_threshold      | 300        |
| key_cache_block_size         | 1024       |
| key_cache_division_limit     | 100        |
| max_binlog_cache_size        | 4294963200 |
| query_cache_limit            | 1024       |
| query_cache_min_res_unit     | 4096       |
| query_cache_size             | 1024       |
| query_cache_type             | ON         |
| query_cache_wlock_invalidate | OFF        |
| table_definition_cache       | 256        |
| table_open_cache             | 64         |
| thread_cache_size            | 8          |
+------------------------------+------------+

14个定点(0.00立)

Answer 1

你们如何测试询问速度? 我怀疑这种做法不是适当的。您对表格的询问越多,就越有可能在桌前做一些倒退的预选,也就是说,尽管需要扫描更多的数据,表上的进一步询问将会更快。之所以如此,是因为MySQL将不必装上磁盘的网页,因为它预置了这些页面。

正如其他人所说的那样,问问问问候会也会使你检验结果,特别是如果他们暗示要连续几次重新开问,以获得“平均”管理时间。

Answer 2

rel=“nofollow”>SQL_NO_CACHE到您的询问中,看看是否是切.的。

http://dev.mysql.com/doc/refman/5.5/en/show-profiles.html 类似:

mysql> SET profiling = 1;
mysql> Your select goes here;
mysql> SHOW PROFILES;

Answer 3

此外,还试图利用更简单的询问:

SELECT * FROM photo_person WHERE `photo_person`.`person_id` = 1

我不知道我的SQL是否选择了你的询问,但从逻辑上说,你和你一样——但你使用的是次座——always<>。尽可能避免分局

友情链接