0

Hi. Can any one simplify the where condition of this mysql select statement? It takes a long time to bring the result or it asks for SET SQL_BIG_SELECTS=1.

In the query below:

The postcode contains values like BH12 or SW10,

The *req_area* contains data like Kensington and Chelsea, SW10,

The region have values like Kensington and Chelsea,

The *town_area* have values like West Brompton, Chelsea. 

select `a`.`user_id` AS `user_id`,`a`.`req_area` AS `req_area`,`a`.`req_area2` AS `req_area2`,`a`.`req_area3` AS `req_area3`,
`a`.`req_property_type` AS `req_property_type`,`a`.`req_bedrooms` AS `req_bedrooms`,`b`.`latitude` AS `latitude`,
`b`.`longitude` AS `longitude`,`b`.`postcode` AS `postcode`
from (`cff_user_property_req_view` `a` join `cff_uk_short_postcodes` `b`) 
where 
(`b`.`postcode` regexp concat("'",TRIM(`a`.`req_area`),'|',TRIM(`a`.`req_area2`),'|',TRIM(`a`.`req_area3`),"'")>=1 or 
`b`.`region` regexp concat("'",TRIM(`a`.`req_area`),'|',TRIM(`a`.`req_area2`),'|',TRIM(`a`.`req_area3`),"'")>=1 or 
`b`.`town_area` regexp concat("'",concat('[[:<:]]',`a`.`req_area`,'[[:>:]]'),'|',concat('[[:<:]]',`a`.`req_area2`,'[[:>:]]'),'|',concat('[[:<:]]',`a`.`req_area3`,'[[:>:]]'),"'")>=1) 
order by `a`.`user_id`;

Thanks in advance.

BlackVegetable
  • 12,594
  • 8
  • 50
  • 82
JIS
  • 1
  • 3

1 Answers1

1

The reason why this is so slow is because your code requires to evaluate three regular expressions on the whole outer product of the two tables. Regular expressions are slow, and anything that has to go through the whole table to find matching rows is rather slow as well. There is little you can do while preserving the exact semantics of the query you have given.

So instead of asking for ways to improove that query, you might be better of describing what it is you're tyring to achieve, and then find a way to model that in a better way. Fulltext search indices might help. Splitting columns into words and storing those words in an extra table might help. I'm not sure whether it would be better to edit your question, or to leave this question as it now stands, and ask a completely new question for that.

You probably should also give an example of what req_area should look like in cases where you expect a match. As the req_area fields are always included in a regular expression, your example won't yield a match, as this long req_area of “Kensington and Chelsea, SW10” is not included in its entirety in any of the other values from your example. Providing some actual examples using sqlfiddle would make it easier for others to experiment with possible queries, thus increasing both the quality of the answers you receive (as the queries have actually been checked) and the chances of receiving any answers at all (because people can go ahead and develop their answers through experiments).

MvG
  • 57,380
  • 22
  • 148
  • 276