How would you normalize / optimize this mysql database?

Question

I have a site for ads with a lot of categories.

When a user publishes an “announcement” on a website, there is some basic information (name, city, price, heading, text, etc.) that needs to be filled out. And also, depending on which category 'the user wants to include the ad, you will need to fill out a few more fields, for example: if the category is “cars”, then the “year” run also appears.

Now my search engine will query the table for everything that the user wants to find ...

My question is: how would you build this database to be the most efficient and fastest?

I read about normalization, but I don’t really understand much of this ... So, whatever you explain, please explain it as thoroughly as possible.

thanks

Answer 1

Normalizing a database usually does not improve speed; it helps remove redundancy and improve consistency.

If speed is the goal, you really need to de-normalize things. Complex joins are usually performance bottlenecks in large databases, and reducing joins by denormalizing tables improves performance.

Answer 2

I would start here to learn about normalization:

http://en.wikipedia.org/wiki/Database_normalization

or here

http://databases.about.com/od/specificproducts/a/normalization.htm

-, , :

http://www.phlonx.com/resources/nf3/

, , , , , , , .

Answer 3

.

Answer 4

, :

• , , , , , .. , , , , , , . .

• , , xml, json . , , , - Lucene .

Answer 5

. 1) , . 2) / : , , . 3) : . , , . 4) - Sachin Chourasiya

Answer 6

, , , , (-), (, , , , , ).

- : .

• 1) : , ,
  • pro:
  • pro: ( , ..)
  • pro: SQL, .
  • con:
  • con: , ( )
• 2)
  • pro: (1)
  • con: SQL
  • con: : 100

, (2) , . , .

: mysql, postgresql : . pg ads, cars, ads (). , .., . vans convertibles, ads, cars, , . (2), ? , ads ( cars cars, convertibles and vans, ). sql: select * from ads where title='foo', ads , , cars . . , / categ , ..

, , , -. , (tm).

---

mysql postgres. pg - , IMHO mysql, . , postgres, mysql, mysql 2.something, 10 , ( ), , , mysql. : " " mysql - myisam, , .. , . IMHO , . , myisam - NOGO. Innodb, , , , .

postgres , , (, ).

ok, , : -)

Answer 7

. , . , .. , , :

• ID

• ID
• ad_id

, .

, , - . "build-database-inside-database". . , , VARCHAR. , (, , / ..) .

Answer 8

MongoDB , .