SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Analyze()

  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Plano
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Analyze()

    through phpMyAdmin, i just discovered the ANALYZE() feature, which is used in the following context:

    Code:
    SELECT * FROM `new_product` PROCEDURE ANALYSE ( )
    it recommends column types to use, and i was just wondering if using these column types is reliable. is it safe to use these or is this feature not very accurate?

    also if i DO decide to switch to the proposed column type, is it safe to just change them using ALTER, or will i probably lose some data.

    thanks

  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)
    as with any automated tool that offers "advice", you need to carefully consider the output to be sure its suggestions make sense for your database. all this tool does is look at the values currently stored in your database and reccommend column types. for example, if you have a varchar(30) column and you never use more than 15 characters, then it will reccomend varchar(15) as the type. or if you have a column with NULL set and none of your values are NULL, then it will reccomend NOT NULL.

    but this may not make sense with your business logic. for example, say you insert records during the day and leave some of the values NULL. then at night you run a process that finds all newly inserted records, does some calculations, then updates all those NULLs with results. in this case, setting the column type to NOT NULL with break your application.

    when i ran it on my databases here, it seemed to reccommend ENUM in a lot of situations where it wasn't appropriate. there are also quite a few experienced members here that strongly discourage the use of ENUM.

    if you decide to change your column types, you might lose data, depending on the conversion. if you change a DECMAL column to an INT, you'll lose anything after the decimal point. and if you change a VARCHAR(30) to a VARCHAR(15), you'll lose the tail end of any string longer than 15 characters. and so on, and so on...

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Plano
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    makes perfect sense, thank you


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
  •