MySQL viewing saved searches

October 31, 2005 / Filed under: Search, SQL

A screen shot of a search box

One of the most useful functions in MySQL is the COUNT() function, which collectively grabs the total number of just about any 'result combination' you can think of.

For example, on a corporate site I am maintaining, I like to keep track of all searches submitted at the site, through our search box. I do this by having each search request entered into a database, at the time of the search.

This allows us to see common phrases being searched for, detect patterns, and adjust the site accordingly.

With the search terms all stored in a database - there are many different ways to view those search terms, for our own studies.

With SQL, we can query for those search terms, using useful criteria.

Below are some examples of how we can use SQL to view our saved searches database.

The most recent search requests:

SELECT * FROM saved_searches ORDER BY datetime DESC

The top 20, most recent search requests:

SELECT * FROM saved_searches ORDER BY datetime DESC LIMIT 20

The most common search requests:

SELECT searchterm, COUNT(*) AS 'Number of times searched'
FROM saved_searches 
GROUP BY searchterm
ORDER BY 'Number of times searched' DESC

The most common search requests, by a specific IP address:

SELECT searchterm, COUNT(*) AS 'Number of times searched', ip_address
FROM saved_searches
WHERE ip_address = '32.433.12.5'
GROUP BY searchterm
ORDER BY 'Number of times searched' DESC

As you can see, there are many different ways to view our saved search reqests. Depending on the extensiveness of the database table structure - the possibilities are almost endless.

Comments/Mentions