Запрос к mysql. Гуру, помогите!

Создайте еще одно поле height в таблице, занесите туда соответствующие данные и выбирайте по этому полю.

Думал. В том-то и причина создания топика. Я хочу решить, что доля меня рациональнее. Т.к. все-таки разобраться в движке и переделать выборку из базы - это тоже работа немалая. ЕСТЕСТВЕННО, если бы я это делал с нуля, то сделал бы по-нормальному :)

у меня доли секунды занимает обработка на PHP для 12000 записей.

На php есть функция microtime() - можно замерить время работы скрипта.

Лучше написать оба варианта выборки (на php и mysql) и сравнить время работы.

Потому что многое зависит от тонкостей программирования и количества обращений к БД.

Вообще mysql должен работать много быстрее, чем php (если не "джойнить" кучу таблиц и не делать сложных выборок из выборок).

Спасибо, попробую

Пример:

create table test1 (

  id int(10) not null auto_increment,

  params varchar(500) default null,

  primary key  (id)

)

insert into test1 set params='{"color":"red", "height":"488", "length":"140"}';

insert into test1 set params='{"color":"red", "height":"377", "length":"289"}';

insert into test1 set params='{"color":"red", "height":"349", "length":"621"}';

insert into test1 set params='{"color":"red", "height":"625", "length":"977"}';

insert into test1 set params='{"color":"red", "height":"159", "length":"945"}';

insert into test1 set params='{"color":"red", "height":"920", "length":"537"}';

insert into test1 set params='{"color":"red", "height":"453", "length":"860"}';

insert into test1 set params='{"color":"red", "height":"671", "length":"292"}';

insert into test1 set params='{"color":"red", "height":"415", "length":"562"}';

insert into test1 set params='{"color":"red", "height":"639", "length":"215"}';

insert into test1 set params='{"color":"red", "height":"579", "length":"698"}';

insert into test1 set params='{"color":"red", "height":"232", "length":"727"}';

insert into test1 set params='{"color":"red", "height":"129", "length":"565"}';

insert into test1 set params='{"color":"red", "height":"210", "length":"899"}';

insert into test1 set params='{"color":"red", "height":"500", "length":"810"}';

insert into test1 set params='{"color":"red", "height":"884", "length":"253"}';

insert into test1 set params='{"color":"red", "height":"877", "length":"730"}';

insert into test1 set params='{"color":"red", "height":"309", "length":"563"}';

insert into test1 set params='{"color":"red", "height":"894", "length":"972"}';

insert into test1 set params='{"color":"red", "height":"599", "length":"448"}';

insert into test1 set params='{"color":"red", "height":"633", "length":"832"}';

insert into test1 set params='{"color":"red", "height":"711", "length":"782"}';

insert into test1 set params='{"color":"red", "height":"632", "length":"958"}';

insert into test1 set params='{"color":"red", "height":"955", "length":"186"}';

insert into test1 set params='{"color":"red", "height":"974", "length":"645"}';

insert into test1 set params='{"color":"red", "height":"290", "length":"432"}';

insert into test1 set params='{"color":"red", "height":"392", "length":"542"}';

insert into test1 set params='{"color":"red", "height":"826", "length":"141"}';

insert into test1 set params='{"color":"red", "height":"772", "length":"846"}';

insert into test1 set params='{"color":"red", "height":"621", "length":"187"}';

insert into test1 set params='{"color":"red", "height":"848", "length":"401"}';

insert into test1 set params='{"color":"red", "height":"886", "length":"991"}';

insert into test1 set params='{"color":"red", "height":"704", "length":"307"}';

insert into test1 set params='{"color":"red", "height":"579", "length":"724"}';

insert into test1 set params='{"color":"red", "height":"875", "length":"309"}';

insert into test1 set params='{"color":"red", "height":"912", "length":"708"}';

insert into test1 set params='{"color":"red", "height":"545", "length":"606"}';

insert into test1 set params='{"color":"red", "height":"841", "length":"915"}';

insert into test1 set params='{"color":"red", "height":"247", "length":"943"}';

insert into test1 set params='{"color":"red", "height":"676", "length":"868"}';

insert into test1 set params='{"color":"red", "height":"969", "length":"616"}';

insert into test1 set params='{"color":"red", "height":"278", "length":"537"}';

insert into test1 set params='{"color":"red", "height":"542", "length":"274"}';

insert into test1 set params='{"color":"red", "height":"857", "length":"345"}';

insert into test1 set params='{"color":"red", "height":"852", "length":"113"}';

insert into test1 set params='{"color":"red", "height":"469", "length":"264"}';

insert into test1 set params='{"color":"red", "height":"632", "length":"780"}';

insert into test1 set params='{"color":"red", "height":"326", "length":"716"}';

insert into test1 set params='{"color":"red", "height":"869", "length":"672"}';

insert into test1 set params='{"color":"red", "height":"286", "length":"772"}';

Ваш запрос:

select * from (select id, params, substring(substring(params, instr(params, '"height":"')+length('"height":"')), 1, instr(substring(params, instr(params, '"height":"')+length('"height":"')), '"')-1) as _height from test1) as t where _height between 400 and 600

create table test1 (

  id int(10) not null auto_increment,

  params varchar(500) default null,

  primary key  (id)

)

insert into test1 set params='{"color":"red", "height":"488", "length":"140"}';

insert into test1 set params='{"color":"red", "height":"377", "length":"289"}';

insert into test1 set params='{"color":"red", "height":"349", "length":"621"}';

insert into test1 set params='{"color":"red", "height":"625", "length":"977"}';

insert into test1 set params='{"color":"red", "height":"159", "length":"945"}';

insert into test1 set params='{"color":"red", "height":"920", "length":"537"}';

insert into test1 set params='{"color":"red", "height":"453", "length":"860"}';

insert into test1 set params='{"color":"red", "height":"671", "length":"292"}';

insert into test1 set params='{"color":"red", "height":"415", "length":"562"}';

insert into test1 set params='{"color":"red", "height":"639", "length":"215"}';

insert into test1 set params='{"color":"red", "height":"579", "length":"698"}';

insert into test1 set params='{"color":"red", "height":"232", "length":"727"}';

insert into test1 set params='{"color":"red", "height":"129", "length":"565"}';

insert into test1 set params='{"color":"red", "height":"210", "length":"899"}';

insert into test1 set params='{"color":"red", "height":"500", "length":"810"}';

insert into test1 set params='{"color":"red", "height":"884", "length":"253"}';

insert into test1 set params='{"color":"red", "height":"877", "length":"730"}';

insert into test1 set params='{"color":"red", "height":"309", "length":"563"}';

insert into test1 set params='{"color":"red", "height":"894", "length":"972"}';

insert into test1 set params='{"color":"red", "height":"599", "length":"448"}';

insert into test1 set params='{"color":"red", "height":"633", "length":"832"}';

insert into test1 set params='{"color":"red", "height":"711", "length":"782"}';

insert into test1 set params='{"color":"red", "height":"632", "length":"958"}';

insert into test1 set params='{"color":"red", "height":"955", "length":"186"}';

insert into test1 set params='{"color":"red", "height":"974", "length":"645"}';

insert into test1 set params='{"color":"red", "height":"290", "length":"432"}';

insert into test1 set params='{"color":"red", "height":"392", "length":"542"}';

insert into test1 set params='{"color":"red", "height":"826", "length":"141"}';

insert into test1 set params='{"color":"red", "height":"772", "length":"846"}';

insert into test1 set params='{"color":"red", "height":"621", "length":"187"}';

insert into test1 set params='{"color":"red", "height":"848", "length":"401"}';

insert into test1 set params='{"color":"red", "height":"886", "length":"991"}';

insert into test1 set params='{"color":"red", "height":"704", "length":"307"}';

insert into test1 set params='{"color":"red", "height":"579", "length":"724"}';

insert into test1 set params='{"color":"red", "height":"875", "length":"309"}';

insert into test1 set params='{"color":"red", "height":"912", "length":"708"}';

insert into test1 set params='{"color":"red", "height":"545", "length":"606"}';

insert into test1 set params='{"color":"red", "height":"841", "length":"915"}';

insert into test1 set params='{"color":"red", "height":"247", "length":"943"}';

insert into test1 set params='{"color":"red", "height":"676", "length":"868"}';

insert into test1 set params='{"color":"red", "height":"969", "length":"616"}';

insert into test1 set params='{"color":"red", "height":"278", "length":"537"}';

insert into test1 set params='{"color":"red", "height":"542", "length":"274"}';

insert into test1 set params='{"color":"red", "height":"857", "length":"345"}';

insert into test1 set params='{"color":"red", "height":"852", "length":"113"}';

insert into test1 set params='{"color":"red", "height":"469", "length":"264"}';

insert into test1 set params='{"color":"red", "height":"632", "length":"780"}';

insert into test1 set params='{"color":"red", "height":"326", "length":"716"}';

insert into test1 set params='{"color":"red", "height":"869", "length":"672"}';

insert into test1 set params='{"color":"red", "height":"286", "length":"772"}';

select * from (select id, params, substring(substring(params, instr(params, '"height":"')+length('"height":"')), 1, instr(substring(params, instr(params, '"height":"')+length('"height":"')), '"')-1) as _height from test1) as t where _height between 400 and 600

Жирно. Спасибо, сейчас только надо умудриться не поламать голову над этим ))

skAmZ

Интересно, и работать по идеи должно быстрей чем лайками.

Есть гораздо более логичное решение.

Нужно создать еще две таблички. В одной табличке будет идти список всех параметров:

id_параметра | название параметра (рус) | системное название параметра

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

id_привязки | id_статьи | id_параметра | значение параметра

Для фильтрации данных по параметрам нужно будет произвести три запроса:

1) запрос списка параметров. На выходе получается список вроде id_параметра - название

2) выборка в таблице с привязками по названию параметра или значению параметра. Результат выборки - список id_статьи с параметрами

3) выборка из статей по id_статьи

При желании результаты первого запроса можно легко кешировать.

Вот и все. Такая штука будет работать очень быстро.