From a performance perspective, how efficient is it to use a MySQL temporary table for a highly used website feature?


Problem :

I'm attempting to write a search functionality for a website, and I've decided upon an approach of using MySQL temporary tables to handle the data input, via the query below:

CREATE TEMPORARY TABLE `patternmatch`
  (`pattern` VARCHAR(".strlen($queryLengthHere)."))

INSERT INTO `patternmatch` VALUES ".$someValues

Where $someValues is a set of data with the layout ('some', 'search', 'query') - or basically what the user searched. I then search my main table images based on the data within table patternmatch like so:

SELECT images.* FROM images JOIN patternmatch ON (images.name LIKE patternmatch.pattern)

I then apply a heuristic or scoring system based on how well each result matched the input and display the results by that heuristic etc.

What I'm wondering is how much overhead does creating a temporary table require? I understand that they only exist in session, and are dropped as soon as the session is ended, but if I have hundreds of thousands of searches per second, what sort of performance issues might I encounter? Is there any better way of implementing a search functionality?



Solution :

What you stated is totally correct, the temporary table will only be visible to the current user/connection. Still, there is some overhead and some other problems such as:

  • For each of the thousands of searches you are going to create and fill that table (and drop it later) - not per user, per search. Because each search most likely will re-execute the script, and "per session" does not mean PHP session - it means database session (open connection).
  • You will need the CREATE TEMPORARY TABLES privilege, which you might not have.
  • Still, that table really should have MEMORY type, which steals your RAM more than it looks like. Because even having VARCHAR, MEMORY tables use fixed length row-storage.
  • If your heuristics later need to refer to that table twice (like SELECT xyz FROM patternmatch AS pm1, patternmatch AS pm2 ...) - this is not possible with MEMORY tables.

Next, it would be easier for you - and also for the database - to add the LIKE '%xyz%' directly to your images tables WHERE clause. It will do the same without the overhead of creating a TEMP TABLE and joining it.

In any case - no matter which way you go - that WHERE will be horribly slow. Even if you add an index on images.name you most likely will need LIKE '%xyz%' instead of LIKE 'xyz%', so that index will not get used.

I'm asking whether a session-specific temporary table to handle the search input by the user (created on a search, dropped on the end of a session) is an appropriate way of handling a search functionality.

No. :)

Alternative options

MySQL has a build-in Fulltext-Search (since 5.6 also for InnoDB) that even can give you that scoring: I highly recommend giving it a read and a try. You can be sure that the database knows better than you how to do that search efficiently.

If you are going to use MyISAM instead of InnoDB, be aware of the often overlooked limitation that FULLTEXT searches only return anything if the number of results is less than 50% of the total table rows.

Other things that you might want to look at, are for example Solr (Nice introduction read to that topic itself would be the beginning of http://en.wikipedia.org/wiki/Apache_Solr ). We are using it in our company and it does a great job, but it requires quite some learning.

Summary

The solution to your current problem itself (the search) is to use the FULLTEXT capabilities.

If I have hundreds of thousands of searches per second, what sort of performance issues might I encounter? Is there any better way of implementing a search functionality?

To give you a number, 10.000 calls per second is not "trivial" already - with hundreds of thousands of searches per second the sort of performance issues you will encounter are everywhere in your set-up. You are going to need a couple of servers, load balancing and tons of other amazing tech crap. And one of this will be for example Solr ;)


Mysql Tutorials

Mysql Howto..

how to avoid deadlock in mysql

How to get the IP address [MYSQL]

How to get unique products_sku with count in mysql query

PHP Question: How to fix these if/elseif statements

How do I count the number of deleted rows in a MYSQL query?

How to extract create statements from different tables of MySQL DBs?

UTF8 charecter shown and not shown in rows

How can I safely give MySQL DELETE power to my script?

How to know when jquery $.get ajax calls have completed?

How to get MySQL value from certain row?

How does privilege users work on MySQL?

How do *you* use auto_increment_increment?

How to host online for free my simple Java webapp (in .war format) and have a mySQL DB for testing [closed]

How to populate an android spinner with data coming from mysql

MySQL LEFT JOIN — How to still return results when leftmost table is empty?

How do I pass binary (blob) data from mysql to imagejpeg for a file save?

How to set Variable to echo 2 possible outcomes based on MYSQL row values

How to select year without date in MySQL and PHP?

How to get the lat/long coordinates of an X mile radius centered at a point via Google maps geolocation or otherwise?

How to Auto increment a column in mysql table through java

How to verify two distinct values belong to a field in MySQL

How will MySql handle an Update that uses the field-to-be-updated in other parts of the query?

How to shorten this MySQL Query (colums use often the same sql parts…)

How to check the time is overlap with the exist record in MYSQL?

How do I remove a MySQL database?

MySQL: How to query for a column and include information from related columns in the same table?

How to assume a table prefix on a entire MySQL script?

how to skip the inverted commas when selecting a column from MySQL

How can I speed up a MySQL query with a large offset in the LIMIT clause?

How to change 0 value to '-' in mysql