SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot ShytKicka's Avatar
    Join Date
    Aug 2004
    Location
    New York
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using DELETE QUICK on HEAP tables

    I am trying to figure out if using DELETE QUICK on HEAP tables is any useful. Basically I am storing session data in the database for each visitor that is visiting my forum, and on each page request this data is being frequently updated and selected. To remove sessions that have been inactive over 30 minutes I have to run a delete.

    Now, should I be running a DELETE or a DELETE QUICK? The mysql.com manual does not explain DELETE QUICK well enough for me to understand. It says to use it when the inserts have "similar" data in the indexed columns. I don't understand, what "similar" means exactly.

    The indexed columns in the table are "session_id" which is a char(32) column that holds a hash value. The other indexed columns are "user_id" which is mediumint() and a column called "content_id" which is a smallint() and corresponds to the type of page you are viewing. Each different page has a different value within a smallint() range. These are my 3 indexed columns.

    So, first I have to ask if it is wise to use DELETE QUICK on a HEAP table? Basically because HEAP tables cannot be optimized.

    Another question is, what would be a difference between DELETE and DELETE QUICK when using it on this type of table?

    And finally if somebody could better explain DELETE QUICK than the mysql manual, then I would be very happy.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    based on your table structure, you should not use quick since your index usage patterns are not predictable.

    plus, i think that delete quick only applies to btree indexes. since heap tables use hash indexes, adding quick does nothing.

    take this example to demonstrate delete quick: create a table with an auto_increment column called id. add 10 records to it such that the id's are 1 through 10. now delete quick records 2 through 9. insert 10 more records. your btree index is now unbalanced; there are holes in the index for records 2 - 9. this is bad because searching an unbalanced tree is slower than searching balanced trees. searching for records by id 11 - 20 will be MUCH slower than searching for records 1 and 10.

    in a table that small, you will never see the difference. but you'll start seeing performance problems with larger tables.

  3. #3
    SitePoint Zealot ShytKicka's Avatar
    Join Date
    Aug 2004
    Location
    New York
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. Then when should people use DELETE QUICK? I still don't exactly understand how it works. I understand that it shouldn't be used on table that have columns that will NEVER be the same, such an auto_increment column, but I do not have a column like that in the sessions table.

    And I forgot to tell you, but yes, I do have B-tree indexes in a HEAP table, there are no restrictions on what type of indexes you may have in a HEAP table. The session_id which is a 32 character hash is a HASH index, while the other 2 columns, user_id and content_id are B-tree indexes. The thing is, many times, the same people visit the page, and have the same session_id, and if the user is a guest he/she will always havea user_id of 0, and the content_id will repeat itself for many users, so the question once again is, would this structre be good for a DELETE QUICK?

    I do not understand DELETE QUICK fully, I read the mysql manual but I can't understand it. I've searched everywhere, and nothing. There's not 1 bit of information about delete quick on google, except the mysql manual, which I've already read.

    Another question is concerning HEAP and DELETE QUICK. What if DELETE QUICK leaves holes in the index, and I have overhead. But, I can't optimize the table since HEAP tables cannot be optimized. Wouldn't that overhead keep increasing and never drop until the session table would become inactive?

    This is all very confusing.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ShytKicka
    The thing is, many times, the same people visit the page, and have the same session_id, and if the user is a guest he/she will always havea user_id of 0, and the content_id will repeat itself for many users, so the question once again is, would this structre be good for a DELETE QUICK?
    in my opinion no. you site usage patterns are probably not predictable enough to keep your indexes balanced. not everybody will hit the pages in the same order at the same frequency. those patterns will change day after day.

    delete quick is useful when you are issuing lots of deletes. basically, quick prevents index optimization from happening during the delete.

    let's say running a particular delete query takes x seconds, optimizing the index takes y seconds, and you need to run delete queries z times in a short period of time. without using delete quick, your time to do that would be z * (x + y). with delete quick followed by optimize, your time is z * x + y. so unless y is near-zero, those time differences will be significant.

    at first glance, it may look like delete quick is the way to go for you since you can save that index optimization time, but don't be fooled. you also have to factor in the performance hit you will get from non-optimal indexes.

    plus, you are using memory tblaes. VERY FAST! the amount of time that you are going to save using quick is insignificant compared to the additional amount of time it will take to search your unoptimized indexes.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •