Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Maximum Rows and coloms in MY SQL Table

  1. #11
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,162
    Thanks
    263
    Thanked 690 Times in 678 Posts

    Default

    You can have multiple indexes on a table. If you have frequent queries, you should create indexes for them if possible.
    Interesting. I haven't ever tried that. I guess it takes more storage space?

    As far as your example goes, a numeric index wouldn't help with searching by title. You'd need to index the titles.
    I was imagining static data; true, if it changes the numbers would be all rearranged. (I think I was imagining an index on that, just unaware of that option.)
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  2. #12
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by djr33 View Post
    Interesting. I haven't ever tried that [multiple indexes]. I guess it takes more storage space?
    yeah, sure. but if it saves you from doing a full table scan, it's certainly worth it. You shouldn't index "everything" (that is a waste), but anything that one of your queries use should be indexed, if possible. For example:
    Code:
    CREATE TABLE phoneNumbers(
        user INT NOT NULL
       ,country VARCHAR(2) NOT NULL
       ,area VARCHAR(3) NOT NULL
       ,number VARCHAR(7) NOT NULL
       ,bestTimeToCall TIME NOT NULL
       ,PRIMARY KEY( country,area,number )
    );
    The primary key is an identifier for the row (all phone numbers *must* be different, so it's a natural choice - no need for an artificial "id" column). MySQL will use it to make sure you don't enter duplicate phone numbers. If you'd like to (for some reason), you can check if a phone number exists in the DB like so:
    SELECT 1 FROM phoneNumbers WHERE country=? AND area=? AND number=?
    And MySQL will use the primary key index to check. It won't look at the table at all.

    Say you want to be able to look up all phone numbers belonging to a certain user -
    SELECT country,area,number FROM phoneNumbers WHERE user=?
    Add an index:
    Code:
    CREATE TABLE phoneNumbers(
        user INT NOT NULL
       ,country VARCHAR(2) NOT NULL
       ,area VARCHAR(3) NOT NULL
       ,number VARCHAR(7) NOT NULL
       ,bestTimeToCall TIME NOT NULL
       ,PRIMARY KEY( country,area,number )
       ,INDEX( user )
    );
    Now you have two indexes, with extra overhead to maintain, but you use both of them regularly and the time they save you more than make up for it.

    Now, say you want to be able to select phone numbers you can call before lunch:
    SELECT country,area,phone FROM phoneNumbers WHERE HOUR( bestTimeToCall ) > 8 AND HOUR( bestTimeToCall ) < 12

    You can index the `bestTimeToCall` column:
    Code:
    CREATE TABLE phoneNumbers(
        user INT NOT NULL
       ,country VARCHAR(2) NOT NULL
       ,area VARCHAR(3) NOT NULL
       ,number VARCHAR(7) NOT NULL
       ,bestTimeToCall TIME NOT NULL
       ,PRIMARY KEY( country,area,number )
       ,INDEX( user )
       ,INDEX( bestTimeToCall )
    );
    But it won't do any good, because the index will include the column values, not the results of the HOUR() function. This is similar to the reason LIKE queries don't benefit from indexes.

  3. The Following 2 Users Say Thank You to traq For This Useful Post:

    djr33 (02-28-2013),letom (03-10-2013)

  4. #13
    Join Date
    Jul 2012
    Posts
    197
    Thanks
    55
    Thanked 3 Times in 3 Posts

    Default

    Thanks for all of your replies, posts and discussions. i hope this thread will get a great record of traffic because important matters are discussed here by experts.

    As the things are in such a way, i think database is extremely satisfied with user needs if we apply the accurate logic in Query processing. But, is there any problem arise in the branch of security and retrieval of data if we store some pieces of data in a file. How can we keep a file safe, what are precautions we need to take to protect a data file.

  5. #14
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by letom View Post
    As the things are in such a way, i think database is extremely satisfied with user needs if we apply the accurate logic in Query processing. But, is there any problem arise in the branch of security and retrieval of data if we store some pieces of data in a file. How can we keep a file safe, what are precautions we need to take to protect a data file.
    I'm not sure what you're asking, here (or if you're asking a question at all). Can you please clarify?

  6. #15
    Join Date
    Jul 2012
    Posts
    197
    Thanks
    55
    Thanked 3 Times in 3 Posts

    Default

    @traq

    Sure!!
    Can u have your suggestions for the following ?
    Is there any problem arise in the branch of security and retrieval of data if we store some pieces of data in a file. How can we keep a file safe, what are precautions we need to take to protect a data file.

  7. #16
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,162
    Thanks
    263
    Thanked 690 Times in 678 Posts

    Default

    You have three options:

    1. Allow everyone to see the page/file (this is not secure).

    2. Create a password-protected file or directory. (One way is using .htaccess; another is to use a server-side language like PHP or a CMS.) Only users with the password can see the file (but others may know that it exists, and can try to guess the password).

    3. Create a protected file that is not available via HTTP. No one can save the file (but you can get it with FTP, and a server-side language like PHP can access its contents just like a database). There are two options:
    --1) protect the file (eg, with .htaccess)
    --2) store the file somewhere that is not accessible via HTTP, usually "above" the root directory (outside of your HTTP folder, probably).
    [--3) put the file where no one will guess. Not really secure. Probably not a real option.]



    Alternatively, you can use a file type that does not get sent to users. This is the case with PHP files. The code is parsed into HTML, and only that generated HTML is ever seen by the users. Technically, anything else in the file is completely secure. This is why database passwords are safe to store in your PHP configuration files:
    PHP Code:
    <?php $database_password 'password'?>
    <html>
    ...
    They will see the HTML but not the code inside the PHP unless you want to show them.
    (Just don't ever accidentally show them the file or accidentally display the password to them, and make sure your server is configured to never allow PHP downloads-- basically if PHP is working, it should be secure. That's true by default on many servers.)



    Another question is why you would want to do this. But you can.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  8. The Following User Says Thank You to djr33 For This Useful Post:

    letom (03-11-2013)

Similar Threads

  1. merge rows within the same table
    By regicidedelferoz in forum PHP
    Replies: 2
    Last Post: 07-06-2011, 01:53 PM
  2. show table rows (new to js)
    By JohnShell in forum JavaScript
    Replies: 1
    Last Post: 09-09-2010, 06:44 AM
  3. setting maximum width for table cell
    By gib65 in forum CSS
    Replies: 3
    Last Post: 04-22-2010, 01:18 AM
  4. update rows in table
    By gurmeet in forum PHP
    Replies: 1
    Last Post: 03-11-2009, 05:19 AM
  5. How to set table cols and rows
    By sinox in forum MySQL and other databases
    Replies: 2
    Last Post: 05-24-2007, 05:14 AM

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
  •