Jump to content


gizmola

Member Since 06 May 2003
Offline Last Active Yesterday, 05:02 PM

Posts I've Made

In Topic: Ajax query is taking about 15 seconds to execute

17 April 2014 - 04:44 PM

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.

In Topic: Ajax query is taking about 15 seconds to execute

13 April 2014 - 09:03 PM

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

In Topic: Ajax query is taking about 15 seconds to execute

11 April 2014 - 03:58 PM

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.

In Topic: Banner ad Rotation

11 April 2014 - 02:39 PM

To make it database driven you simply need to create a banner table.

You'll want to have a structure like this:
 
id int (primary key)
url varchar(512)
image varchar(128)
active tinyint
As for rotation, so long as your banner ad table doesn't get larger than a thousand rows, there isn't much of a concern, however, I would highly recommend reading this blog post: http://jan.kneschke..../order-by-rand/

With that said, the simplest solution for you, that will certainly work fine for quite a while:
 
SELECT * FROM banner WHERE active = 1 ORDER BY RAND() LIMIT 1
If you expect rotation over time without reloads, then cyberRobot's point should be taken into account. Often people will utilize an iframe and place the banner in the iframe, which can then have a simple meta refresh tag in the header of the code.

Ajax polling offers a more sophisticated option to cyberRobot's point, or you could alternatively fetch a number of ads initially and use a javascript timer to rotate them.

In Topic: Need help with a mysql query.

10 April 2014 - 03:04 PM

Jackques1. Thanks for the reply... I guess I will have to implement it... looks like I have some work to do for tomorrow :)


I agree strongly with everything Jacques1 wrote.

In regards to prepared statements on insert, IF you have multiple inserts to perform at one time, it is faster to do it with prepared statements, but rarely are there situations where the performance increase makes a difference. Most inserts are done one at a time, although it's always good to know that you have the option of doing a few if needed, and that those will be optimal.

However, as Jacques1 stated, it's simply more secure to use the prepared statements and variable binding, and that's reason enough. You also lessen complexity, because you no longer have to worry about escaping strings.

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com