benchmark for this 2 sql statement.

Posted on 2006-04-23
Last Modified: 2011-09-20
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 (


Which one if faster, better from how long?
Question by:wjh7554
    LVL 20

    Expert Comment


    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).

    Author Comment

    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.

    LVL 20

    Accepted Solution

    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

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now