ситуация такая есть база на винде на денвере и на хостинге.
версии на винде:
1.
2.
3.
4.
5.
6.
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.40-community |
+------------------+
На хостинге:
1.
2.
3.
4.
5.
6.
mysql> select version();
+----------------+
| version() |
+----------------+
| 5.1.68-cll-lve |
+----------------+
имею таблицы:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
create table lookup
(
ID int(11) NOT NULL AUTO_INCREMENT ,
brand_name text,
art text,
art_disp text,
art_id int(11),
art_bra_md5 binary(16) NOT NULL,
KEY in_art_id (art_id),
KEY in_s_md5 (ART_BRA_MD5),
PRIMARY KEY (ID)
);
CREATE INDEX ind_art ON lookup (art(8));
create table pre_add_lookup
(
n int(11),
bra text,
disp text
);
таблицы на обоих базах идентичны, индексы на lookup идентичны на обоих базах.
делаю запрос
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select
p.n,
p.bra,
p.disp,
(select l.brand_name from lookup l where
l.art= REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.disp," ",""),"-",""),".",""),"/",""),"#","")
group by l.brand_name
) as new_bra
from pre_add_lookup p
where p.bra = "N/A"
and
(select count(DISTINCT l.brand_name) from lookup l where
l.art = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.disp," ",""),"-",""),".",""),"/",""),"#","")
) = 1
на денвере все ок, на хостинге индексы использовать отказывается...
использование use/force index не помогает...
вот такая ботва на денвере
1.
2.
3.
4.
5.
6.
7.
+----+--------------------+-------+------+---------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+---------+---------+------+-------+----------------------------------------------+
| 1 | PRIMARY | p | ALL | NULL | NULL | NULL | NULL | 19890 | Using where |
| 3 | DEPENDENT SUBQUERY | l | ref | ind_art | ind_art | 11 | func | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | l | ref | ind_art | ind_art | 11 | func | 2 | Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+---------+---------+------+-------+----------------------------------------------+
а на хостинге
1.
2.
3.
4.
5.
6.
7.
+----+--------------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
| 1 | PRIMARY | p | ALL | NULL | NULL | NULL | NULL | 19890 | Using where |
| 3 | DEPENDENT SUBQUERY | l | ALL | NULL | NULL | NULL | NULL | 136879 | Using where |
| 2 | DEPENDENT SUBQUERY | l | ALL | NULL | NULL | NULL | NULL | 136879 | Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
что может быть...
соответственно на денвере считается мгновенно а на хостинге часами...
