Maximum Rows and coloms in MY SQL Table

[djr33]

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

[traq]

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:

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:

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:

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.

[letom]

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.

[traq]

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?

[letom]

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

[djr33]

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