0

I have a database of more than 30 000 quotes and sayings. It works separately in subdomain, but some of the quotes, c. 300, are used in another part of the site - they are present in view files as text (until recently in plain html file, no PHP framework).

Now I wish to modernize the solution and source the quotes from the large database - so that I could list quotes related to a blog post (several out of the pool of 300) and get a link to the view of the quote in subdomain.

Problem: If I generate views and parse the 32 000 records each time a page is generated, it might influence performance.

What would be best or second-best solution to go easy on the database... - maybe creating in PHPMyAdmin a view with a selected collection of quotes? - maybe a new table in which one column would be a foreign key identyfiyng the quote in the BIG table?

I am just a beginner, can't think of a good way to do it.

Thx!

Edit: I use a simple shared hosting - this is my personal collection of wise sayings. I just wish to use good practices and minimize the risk that I exceed my server quota. libmysql - 5.5.25a phpMyAdmin 3.5.8.1

Someone helped me a few years ago and he did these two tables: Table quotations category_id, author_id, orig_language_id, source_id, year, notes, deepness, created, project

Table quotation_texts quotation_id, language_id, text, created

This way the quotes can be translated into many languages, which was the collection's target capability.

THE DESIRED FUNCTIONALITY/PROBLEM TO SOLVE: Within the site there are many blog posts within which I wish to use quotes IN RANDOM PLACES.

Now they work as plain text generated inside view files. I want to

  • invoke the quotes from database In similar way works the NEXTGEN Gallery in Wordpress - individual photos are invoked by command [singlepic=2415,420,300] where the first number is the ID of a photo.

  • make a view with all quotes invoked in all posts (the list would be updated automatically as I publish new posts, of course)

The only solution I came up with is to make another table - post_quotes. And in one column put the quotation text copied from the existing table.

Peter
  • 2,634
  • 7
  • 32
  • 46
  • 2
    Please describe your database in technical terms. What table definitions does it have? Which MySQL engine is it using? By the way, the word *huge* ordinarily is used to describe databases containing on the order of 10^9 rows, not 10^5. Your application is well within the capabilities of data base technology. – O. Jones Dec 26 '14 at 01:27
  • 3
    30,000 text records with quotes at a guess is around 100-200 MB, that is not an enormous about of data by any standards. That said without describing what you are trying to do, i.e. what are you looking to do, get the most recent messages, random messages, text matching etc etc it would be difficult to suggest a non all-inclusive strategy to what is a rather vague problem. – Lucas Dec 26 '14 at 01:43
  • splits your large tables or use table inheritance – Vivek S. Dec 26 '14 at 05:19
  • Well seems you never use Database before, give it a try you will be amazed the DB can handle millions of record 30K is child play for them. IF you can give structure we can help optimize it. – Sumit Gupta Dec 26 '14 at 05:26
  • @mr.incognito I am a noob - can you suggest a good tutorial for that SumitGupta what exactly you need above what is already in my original question? – Peter Dec 26 '14 at 06:00
  • 1
    @PiotrPlebaniak just Google it man http://stackoverflow.com/questions/17905116/mysql-the-quickest-way-to-split-a-big-table-into-small-tables ,http://stackoverflow.com/questions/18646837/implementing-inheritance-in-mysql-alternatives-and-a-table-with-only-surrogate, http://dba.stackexchange.com/questions/36573/how-to-model-inheritance-of-two-tables-mysql – Vivek S. Dec 26 '14 at 06:03

0 Answers0