• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

benchmark for this 2 sql statement.

I am having problems on preforming one of my sql statement where by i need to check the existance of the data.

boolean found=false;

Select * from Table_1 where column_1 = '"+username+" ' ";i

if (rs.nest())
  found = true;

***************

I am thinking instead of using the conventional SELECT statement, do you think the perfomance will bb much better if i use this,

boolean found = false;

Select * from Table_1 where MATCH (column_1) Against ('"+ username +" ' ") IN BOOLEAN MODE;

if (rs.next())
  found=true;

**********

Which one if faster, better from how long?
0
wjh7554
Asked:
wjh7554
  • 2
1 Solution
 
virmaiorCommented:

In answer to your question,
well the speed of the two statements will vary depending on whether you've got an INDEX on the table.  If both indexes were present, then I would say that the = method would be faster than the fulltext method.  But if the table is properly indexed, then they should both be really fast.

IF you're just trying to check if data exists... then I would also recommend adding a " LIMIT 1" to the end of your statement since the number of entries has no value to the data check.  (this could be the cause of any slowness that you're experiencing since even though you're only using 1 row, the DB retrieves all the rows that match your query if you don't specify a limit).
0
 
wjh7554Author Commented:
can you give me some experience how to ultilise the "LIMIT 1"

Yes, i am comparing all senario whereby all the Primary Key, FullText Index are completely set properly.

0
 
virmaiorCommented:
you just put it at the absolute end of a mysql query.

Select * from Table_1 where column_1 = '"+username+" ' " LIMIT 1;
Select * from Table_1 where MATCH (column_1) Against ('"+ username +" ' ") IN BOOLEAN MODE LIMIT 1;

technicaly LIMIT can take two terms, but it assumes 0 for the second if you don't tell it.  
LIMIT <number of records to retrieve>, [offset from first relevant record]

so by putting a LIMIT 1 on the end mysql will stop retrieving records when it finds the first one
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now