one big database or several small ones.

[defiance]

hi

i have a quick question here.


i have a few thousand or almost hunderd thousand names.

user would be able to search the names.

my question is;

is it better to have one big database

or

several databases containing the same first leter of alphabets?

thank you for yuor opinion.

[Cheeky Chino]

One database several tables

[defiance]

hi

thank you for your reply.

oh..........

yea.

actually i meant to that.

so, it would actually be

one database with one table
or
one database with a-z table
thanks

[r937]

no, it would be better to have one database, one table

consider: what is your sql to count how many entries you have for each letter?

and anyway, 100,000 is small

100,000,000 is medium

100,000,000,000 is large

do not worry about 100,000, mysql can handle it easily, assuming you index the search columns properly

[Smirnov]

You might also want to make an extra column with the first letter then .

[r937]

good idea (shows you're thinking), but that extra column might not really work

the selectivity might not be good enough to ensure that an index will always be used on it, especially if searches are for specific names and not just always all names with that first letter

[defiance]

thank you for all of your suggestions.

so,

i would have one database and one table...

thank you very much.

ermm....

you mentioned indexing...

do you any good article for me to read about indexing?
..........

newbie here..........

[r937]

what did you think of the information about indexing on the mysql site?

did you like it? did you understand it?

[samsm]

do you any good article for me to read about indexing?

This article is old, but explains the concept nicely.

http://www.sitepoint.com/article/opt...ql-application

[defiance]

i read it...

but didnt give me much stuff.

i mean,

that article will be good if i do have some background knowledge abt indexes.

but, as a total newb, i really still don get what IS index....

well may be it is just me.

but thank you so much for the recoomendation...

have a nice day

[r937]

okay, i'll give you a simple example

grab your telephone book, white pages

it's in sequence by last name, then first name, then address, right?

okay, i want you to give me a list of all the people that live between 100 Main Street and 200 Main Street

please post back here when you have the answer

[defiance]

i am in school now, cant find any phone book around.

i will post here tonight again.



thanks for the reply though......

[Mickj]

I Added something like that to my Db When I started it as it relates to Artists and groups I needed to handle things like 3rd edge = thi and say 411 = fou
I decided to use it only for the sorting and have a max of three charactors for better sorting which seems to work nicly in my main Db.
I have it on my site as well as both db's which are synced where need be but havent had a use for it as yet but its available should I need to do things more in the way I do in my main db.
Not too sure about the indexing for the above as I don't search on it any advice r937.

thanks

mick

good idea (shows you're thinking), but that extra column might not really work

the selectivity might not be good enough to ensure that an index will always be used on it, especially if searches are for specific names and not just always all names with that first letter

[r937]

mick, are you typing your posts one-handed (or one-thumbed) into a cell phone or something?

because the sentences are sort of run-on and hard to understand

[Mickj]

mick, are you typing your posts one-handed (or one-thumbed) into a cell phone or something?

because the sentences are sort of run-on and hard to understand

Sorry r937 always had problems putting things into words hope you understand.

[defiance]

hi

i am back...

i cant find any phone book ard here...

i have SBC yellow pages...

but they list business..

so the list of ppl that you asked me to find, let me just make it up alright?

1. John Kean | 100 Main St
2. Honey Dew | 101 Main St
3. David Star | 102 Main St
4. Ryan Sean | 103 Main St
5. Monte Stephen | 104 Main St
6. Chapman James | 105 Main St
7. Steven Shore | 106 Main St
8. Chuck Smith | 107 Main St
9. Tom Cason | 108 Main St
10. Eric Shore | 109 Main St


is this data too little to be indexed??

anyhoo

thank you very much

[r937]

i don't think you understood what i was getting at

in order to find those people, you had to read the entire phone book!

here's another example

which of these authors wrote their works in german? --
http://www.kirjasto.sci.fi/indeksi.htm

[defiance]

hi

i think i am getting you now..

so, in order to find out which author who wrote in German, i would need to go to each individual page and find out in their "selected works"

thus

i can relate this to indexing.

now, lets relate this finding author in indexing.

==========================================

so i would have to index the field "Selected Works".

but i still don actually get the meaning of indexing,

taken from the mentioned article "If we create an index on the “name” column, MySQL will automatically order this index alphabetically"

so once we make an index in the "selected works" mysql would order them alphabetiaclly.


"So if I want to get the value of peopleid when the name is Mike (SELECT peopleid FROM people WHERE name='Mike', MySQL can look in the name index for Mike, jump directly to the correct row in the data file, and return the correct value of peopleid (999). MySQL only has to look at one row to get the result. "

so when we are indexing, we are actually asking mysql to 'remember' or 'cache' the field and its values, am i right?

like during an open book exam, chapter 12 is being tested.
so you need to 'remember' or 'learn' or 'cache' chapter 12 first to be able to do the exam in much shorter time because you have read the chapter and know where is the answer for the given question, am i right?

whereas

if you havenot 'read', 'remember', 'learn', 'cache' chapter 12, during the exam you would need to actually 'read' first page of the chapter until the end to find the answer, and that would take much longer time.


am i right about indexing in relation to my chapter 12 exam, chapter 12 being the field that we are searching???


thank you so mcuh.

[samsm]

It's not really caching, it is more like a cross-reference table.

Think about an index in a book. That is pretty much exactly the way a database index works. Surely one of your textbooks has an index, but if none do look at a a cookbook.

[r937]

the index on the white pages phone book that i was trying to get you to understand would be a list of people in sequence by their street address, not by their lastname/firstname

there actually are books like this, but they are usually available only in public libraries

thus, you could jump to the page in that index that starts with M, scan ahead until you get to Main Street, then read the people's names who live between 100 and 200 Main Street

to get the same answer from the ordinary white pages phone book, you have to read every single person in the entire phone book (a few hundred thousand entries? a few million? i guess it depends on the size of your city), check each one to see if that person has an address between 100 and 200 Main Street, write down any that you find, and a couple of years later, when you're finished, you would still have to sort the ones you found into the right order

that's the equivalent of a "table scan" where the database has to read the entire table to find the rows you want

with an index, it can just "jump to" the Ms in the index, and scan through the Main Street addresses real quick, and voila, they're also in the right order

[defiance]

oookkkk............
i understand now...............


ermm....

thanks alot...


i ll post questions if i have any ques then

thanks once again