Creating a Temporary Table for searches 1

[solepixel]

I have a search setup on an intranet site for my company that works pretty good. I do wish it would work a little better, but I'll have to settle for what I have. Let me explain what I'm doing. When a search is made, I run the word through several tables in my database and use FULL TEXT matching on certain fields in those tables to produce results. I have it set to BOOLEAN MODE so "prod" returns results with "product". As I search through each table, I insert each result into a table I create based on a user's session id. Once I check all tables, I then then run one last query to display the results. Then I drop the table created at the beginning of this process.

The problem is, sometimes the table doesn't get dropped. I don't know why or how, but I end up with a few empty tables in my database at the end of the month. Any ideas as to what could be causing this?

Also, my other issue with this process is I'm using a 'relevance' field to get a "score" of each result, however it seems with BOOLEAN, they all return with a score of "2" or "1", which results in an irrelevant sort order. When I was using standard FULL TEXT search without BOOLEAN, it would return some percentage (like .22340580212). Problem with that method though was it wouldn't return "product" when searched for "prod". So my questions are, i guess:

1. Is there a better way to do this?
2. Is it possible to have the best of both worlds with BOOLEAN and standard FULL TEXT searches?

And the reason i'm not using "LIKE" anymore was 1. I hear this way is much more efficient and 2. there isn't the relevance (or score) feature with LIKE.

 

[piti]

1 - have you tried with temporary tables?
2 - i'm not so sure that the boolean mode will do what you are describing, it does not use any other logic, it only extend the functionality by using operators and disables the treshold limit ... and now to that relevance - in boolean mode mysql does not calculate the relevance as in normal mode, so what you can do is have the relevance field calculated without boolean mode while in where statement still with it ...

 

[solepixel]

Ok, so
2 - i removed the IN BOOLEAN MODE from the "as relevance" part, and i think it is working right. Thanks for that.

1 - I would like to use temporary tables, but I don't really know how temporary tables work. Maybe you can help. When my search begins, I go through these steps:

1. Drop table [sessionid]
2. create table [sessionid] 
   ([sessionid]_id BIGINT( 255 ) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   alpha` VARCHAR( 255 ) NULL,
   `meat` LONGTEXT NULL,
   `link_text` LONGTEXT NULL,
   `additional` LONGTEXT NULL,
   `relevance` DECIMAL( 50,30 ) NULL
   ) ENGINE = MYISAM
3. Run through all my tables with search string
4. While running through, insert results from each table into [sessionid] table.
5. Setup my limit/pagination/sort order
6. Run last query on [sessionid] table.
7. Drop [sessionid] table.

I'm not quite sure at what point do i create a temporary table, nor the syntax, nor do I really know what you can and can't do with temporary tables.

Thanks for the help.

 

[piti]

temporary tables are created just as regular tables by just specifying the keyword TEMPORARY - CREATE TEMPORARY TABLE ...
temp tables are "connection" specific - so you can have the same tablename for all connections as this temporary namespace is unique for all connections (you can even have a temp table named as a regular table), of course you can use your sessionid approach
in your case you would amend your steps:
1 - CREATE TEMPORARY TABLE IF NOT EXISTS ... - specify the if not exists statement in case the user searches several times without closing the connection to mysql as temp tables are dropped after the connection is closed
2 - delete data from the temp table
3 - fill the table, select from it, ...

 

[solepixel]

I've tried this:

CREATE TEMPORARY TABLE IF NOT EXISTS `[sessionid]`
	( `[sessionid]_id` BIGINT( 255 ) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`alpha` VARCHAR( 255 ) NULL...

	...`relevance` DECIMAL( 50,30 ) NULL
	) ENGINE = MYISAM;

and I keep getting this:

Access denied for user 'username'@'%' to database 'db_name'

 

[piti]

grant the user "craete temporary table" privilege

 

[solepixel]

I'm using SQLYog and I can't find where that privilege can be set. Is there another way to do that on an windows iis server? or can it be done through php? My user has all privileges, but maybe that one needs to be specifically set.

 

[solepixel]

Ok, here's the query that updated my permission:

grant create temporary tables on database.* to username;

i got that to work now. Thanks a bunch piti

 

[solepixel]

Ok, I noticed a problem. I need to clear out the data in the temporary table each time. It is accumulating multiple searches in 1 table. How do I drop or truncate a temporary table?

 

[piti]

providing that you just updated the your logic (where you were creating and droping the table) with my suggestion - that means you attemp to create temp table, delete the contents and populate it - there is no way that the search results are accumulating ;-)
but you can truncate any table with (here comes the magic ) TRUNCATE <tablename> statement

 

[solepixel]

...that means you attemp to create temp table, delete the contents and populate it...

The only thing I've done is change CREATE TABLE [sessionid] to CREATE TEMPORARY TABLE IF NOT EXISTS [sessionid].

At no point am I deleting the contents. So If I search for "stuff" it appears fine, then i search for "stuff" again and there are 2 of each result, and if I search for "stuff" again, yep, you guessed it, 3 of each result.

I tried "TRUNCATE [sessionid]" and it tells me the table does not exist. So what am I missing here?

 

[piti]

you should not use just excerpts from the posts, that might lead to something working ;-)

so once again - temp tables exists for the connection you used to create them and are valid only until you drop that connection (or you explicitly drop the table) so when you try that truncate statement afterwards, of course there is no table with that name

if you just changed your create statement and the drop at the end of your logic is executed there should not be any table with that name, likewise the next time search process is run the temp table is created and is empty

so first check for errors after that drop

 

[solepixel]

Ok, sorry about that. The drop table only worked with a query that first checked to see if it existed. The query was a bad way of checking so i just took it out since I know that the table will always exist, since we are creating it on the same page.

I think this works for now. Thank you so much for the help and sorry for the miscommunication.