Jump to content


Photo

How should I set up my MySQL?


  • Please log in to reply
2 replies to this topic

#1 lpxxfaintxx

lpxxfaintxx
  • Members
  • PipPipPip
  • Advanced Member
  • 181 posts

Posted 23 August 2006 - 01:26 PM

I have a database of the english dictionary. I was wondering how I should set up my db...

Should I make 1 table and insert all the words in there (a LOT of words)-- or should I make 26 tables, for each letter of the alphabet. Would the second choice make the queries run faster?

Thanks

#2 Woolf

Woolf
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 23 August 2006 - 02:41 PM

I would think the second choice (26 tables) would be better, but that's just my opinion. My reasoning behind this is:

If you were to use one table, with hundreds of thousands of words... that'd be a big table, right? Well, just think... in order to query the results by letter, you would either have to a) create a 'first letter' column or b) pull all the results from the table and then check to see if they contain the correct first letter. Even if you were to use the 'first letter' column, you would still have to use extra processing by using WHERE first_letter='x' (where x is the letter).

So, if you were to use 26 separate tables, when you run your query you would just have to do it like normal. (SELECT * FROM letter_x ORDER BY word ASC) That's really quite simple, and I would think that it would be much faster. Especially with pagination.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 August 2006 - 03:42 PM

That really depends on what you're using it for... you do really want to query 13 different tables?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users