Ajax query is taking about 15 seconds to execute

[oracle765]

SELECT
        COUNT(apl.Location) as Hotel , apl.Location , apl.StateProvince , prl.RegionName , apl.Country, cl.CountryName, ptl.PropertyCategoryDesc
        FROM activepropertylist apl
        INNER JOIN CountryList cl ON apl.Country = cl.CountryCode
        INNER JOIN PropertyTypeList ptl ON apl.PropertyCategory = ptl.PropertyCategory
        INNER JOIN ParentRegionList prl ON apl.RegionID = prl.RegionID
        WHERE
        (apl.Location LIKE '%$q%' OR apl.City LIKE '%$q%' OR cl.CountryName LIKE '%$q%' OR prl.RegionName LIKE '%$q%') GROUP BY apl.Location ORDER BY apl.PropertyCategory LIMIT 25

Hi All

 

just wondering if there is a way to speed this query up with creating indexes or make the query itself perform better, basically it is an autosearch dropdown box for hotel listings in a given place and is running very slow

 

EG Brisbane, which should show Bribane, Australia,AU (6) Hotels, Brisbane, United States of America, USA, (9) Hotels

 

 

 

[Barand]

Have indexes on Location, City, CountryName, Region and use "=" instead of LIKE so it can use those indexes. Better still, use IDs for location, city etc in the search, where the user can select those IDs from dropdowns, to avoid spelling errors in the user input of search terms.

[oracle765]

Hi Barand, thanks for that

 

I am still not sure what you mean by using id's for location, city etc in the search

 

please advise

 

thanks

Alan

[Barand]

You should not have multiple occurences of the word "Brisbane" (or any other city name). "Brisbane" should be stored once in a location table and the id of that record should be in your activepropertylist table. In other words, your data should be normalized. EG

+------------+
| Country    |
+------------+
| countryid  |---+
| countryname|   |
+------------+   |
                 |    +------------+
                 |    | Region     |
                 |    +------------+
                 |    | regionid   |--+
                 |    | regionname |  |
                 +---<| countryid  |  |
                      +------------+  |
                                      |   +---------------+
                                      |   | Location      |
                                      |   +---------------+
                                      |   | locationid    |--+
                                      |   | locationname  |  |
                                      +--<| regionid      |  |
                                          +---------------+  |
                                                             |
                                                             |    +-----------------+
                                                             |    | activeproperty  |
                                                             |    +-----------------+
                                                             |    | propertyid      |
                                                             +---<| locationid      |
                                                                  | propertytype    |
                                                                  | etc.            |
                                                                  +-----------------+

[gizmola]

Just to elaborate on what Barand was saying, whenever you use a LIKE query with a wildcard IN FRONT of the search criteria:

 

LIKE '%$var%'

Then an index can not be used. The entire table has to be scanned.

 

Since you have this:

 

(apl.Location LIKE '%$q%' OR apl.City LIKE '%$q%' OR cl.CountryName LIKE '%$q%' OR prl.RegionName LIKE '%$q%') GROUP BY apl.Loca

Well you are table scanning the location, city, country and region tables at very least.

 

A query that ends with a wild card however, will use an index:

 

LIKE '$var%'

And of course an exact match is best.

[oracle765]

ok sort of understand, but we have data in that table relating to more than one occurrence of the city

 

EG

 

Brisbane, USA

Brisbane, AU

Brisbane south. AU

Brisbane City near big wheel, AU

Brisbane North, USA

 

and so on

 

please advise

[gizmola]

As long as you are searching for 'Brisbane....' then LIKE '$var%' will use indexes where they exist.

[oracle765]

Hi all

 

I am still confused by this, I have just noticed that some of them say East Brisbane, or, Near Brisbane convention center and it does not find these

 

I gather that '$var%' will only find starting with Brisbane.  So my question is what type of index would be best a spatial, full text i am not sure

 

thanks all

[gizmola]

The only type of index that will allow for finding a sub string inside a string with any sort of efficiency, is a full text index. There are also specialized search engines you can use (and most frequently these are what is used to provide searching against a number of facets) -- like sphinx, elastic search or solr.

 

You can apply mysql full text indexes, but there are caveats and a lot of things you have to learn about in order to use them, although the learning curve and deployment issues involved with full text engines are similar.

 

I would suggest you do a bit of research, and certainly you can experiment with adding some mysql full text indexes.

 

If you do go that route, make sure you drop any of the other indexes you've created specifically to speed your search.