General MySQL performance question

jnutter

Hey All,

Just a general "seeking advice" question here:

I'm building a database app that stores potentially huge text-based documents in the database (it has to be done this way to maintain the structure and relations etc etc).

In terms of the end product, ie. searching this content etc.. would it be better for me to shove all this content into one big table or is it smarter to have the application build individual tables for each document?

When people are searching they will only be searching SOME of the documents so I figured the individual tables approach would at least cut this down a bit... but is there some hit MySQL takes from searching multiple tables that I'm not aware of?

Any advice, as always, is much appreciated =)

nutter

Anon

MSSQL > MySQL

[deleted]

Ehm.. ya, that helps a lot.

To anwer the question:

One big table = lots of data to read.
Several tables = lots of data to read.

The only advantage of using several tables would be in locking. You could update/insert records into one table without locking the rest of the tables.
If you have one large table, an insert/update would lock the whole table (MySQL's lovely table-level-locking)

Other than that, having several tables for each document just makes your life very unpleasant:

Every new documenty would need a new table.

You'd have to store the name of that new table into a cross-reference table so you can find out which table a document is in.

Searching on all the documents at once (if you are looking for a doc that contains a certain string) would be nearly impossible. You'd have to query all the tables (after findout which ones you have), and you can't create any good indexes for searching, because indexes only work for a single table. And having an index on a table that contains just one record is not very usefull. So you are doomed to using sequencial scans (reading all the data in the record every time).

Splitting a table into several smaller tables just isn't very efficient if you still want to access the data asif it was in one big table.

jnutter

Hmmm... well I'm not worried about table locking as the public side of this thing is going to be read-only. (only the site admin will ever update these tables).

I'm also not worried about the extra work involved with many tables... careful naming conventions solves that problem with ease.

Now, lets say there were about 4000 documents to go in here, each with anywhere from 1-500 pages (the average being around 50 pages). Now, for other reasons I won't go in to now, each "page" needs its own row in the database.

When doing searches on this data I expect to only be searching 5-50 of these 4000 documents at any given time (people purchase access to docs and can only search the ones they have access to). My searches are primarily going to be LIKE keyword searches on the big text fields.

In THAT sort of scenario can you see any advantage to breaking them down into smaller tables?

... I can see with individual tables for each document I can list the tables to do the select from and it will only have to search those tables... but is that in any way faster than just dumping them all in one table and doing a "WHERE doc_id IN (1,2,3...)"

see what I'm sayin?

jnutter

MSSQL is not OPEN SOURCE.

I need not say more.

neuromancer

Microsoft SQL Server is far better than MySQL. Use the right tool for the right job.

If you are running a small database and site MySQL might be fine for you but when you need a enterprise size job done its either oracle or mssql.

neuromancer

the main thing you want to remember is you can't hit any index if you use a wildcard as the first character in a LIKE with any SQL server.

jnutter

Ok... my first instinct is to say: PROVE IT.

But seriously though... I am NOT working in a Windows environment and my client cannot afford to buy Oracle Site Licences (MySQL if I need to remind you is free and also entirely platform independent).

Secondly, just tossing around "this sucks and this other thing is better" without backing it up with at least ONE reason is rather pointless no?

Thirdly, I have built quite a few "enterprise" size sites using MySQL over the past couple years and I have had NO problems with it whatsoever. (ok the lack of transaction roll-backs is a bit of a nuisance, but the answer to that is simply intelligent design)

And finally, my question (if you read the subject line) pertained to MySQL not MSSQL so take your microsoft advocacy elsewhere please and thank you =)

nutter

neuromancer

Lets start listing things that can't be done with MySQL

Forign Keys,
Transactions,
Replication,
User defined functions,
Views,
2G+ tables,
Text indexing,
Subqueries,
XML,
Rules (Data constraints)

"transaction roll-backs is a bit of a nuisance, but the answer to that is simply intelligent design"
No, you need rollback transaction if you have done any serious database work. Transactions are very important.

"And finally, my question (if you read the subject line) pertained to MySQL not MSSQL so take your microsoft advocacy elsewhere please and thank you =)"
Im just pointing out MySQL might not be the best option.

Im sure thats only a few of them, all of the things mentioned above are very powerful when used correctly and help with our application development in ways you could not belive. I can't see using MySQL for anything serious after using MSSQL for a few years.

thx drive pass ^{_^.}

[deleted]

In THAT sort of scenario can you see any advantage to breaking them down into smaller tables?

No, because you say you are only accessing specific records, so you only access the records that belong to a particular document. All the rest (even if there are 50million other records) is not accessed. If you have a good index on the document-indtification data, doing a query on 5 out of 10 records should not be noticably faster than getting 5 out of 5 million records.

On the other hand, there is quite a bit of speed difference between running 5 or 50 queries, and between running a query on 5 or 50 tables.

Neuromancer has a good point: the LIKE operator doesn't use any indexes if you start the search with a wildcard. Instead, look at FULLTEXT searches (in the MySQL manual)

[deleted]

Lets start listing things that can't be done with MySQL

Ehm... lets start by getting the facts straight

Forign Keys,
Transactions - MySQL DOES support transactions
Replication - MySQL DOES support replication
User defined functions - MySQL DOES support user defined functioins
Views,
2G+ tables - MySQL supports tables up to the size of your filesystem
Text indexing - - MySQL DOES support fulltext indexing
Subqueries - still not, and it's a major nuissance
XML - Is that anywhere near relevant?
Rules (Data constraints)

"transaction roll-backs is a bit of a nuisance, but the answer to that is simply
intelligent design"

No, you need rollback transaction if you have done any serious database work.
Transactions are very important.

For most web-based apps you don't actually need transactions.
But if you are into online shopping etc, they are a must

Im just pointing out MySQL might not be the best option.
Indeed, PostrgeSQL is much better and supports allmost everything.

Im sure thats only a few of them, all of the things mentioned above are very >powerful when used correctly and help with our application development in
ways you could not belive. I can't see using MySQL for anything serious after
using MSSQL for a few years.

Can't imagnine I'll ever use MS-SQL, I much prefer Informix and PostgreSQL.
But that's a matter of taste.

                          thx drive pass ^_^.

neuromancer

MySQL has come a long way if they support that much stuff now, last time I did heavy evaluation on it was well over a year ago.

As for XML, we use XML in MANY places that we need dynamic unknown data or when exporting data to a file for crystal or other apps (SELECT x FROM y FOR XML AUTO)

And I forgot to mention the most important thing, stored procedures.

[deleted]

Indeed they have. It's still a tiny database thingy compared to Postgres and the commercial stuff, but it's a good place to start.

As for the XML hype, in the old days we called that a flatfile or ascii interface.
But I couls go on for hours about that. 🙂

The lack of stored procedures in mysql goes with the fact that MySQL is not a true Rdbms, there's no R in mysql (yet).

Still that doesn't mean MySQL can't be used for large enterprise-level apps. You just have to be very carefull about what you do.

The same goes for PHP. Java programmers would argue that PHP is not suitable for enterprise level apps, because there's no compiler to tell you what you are doing wrong. I tend to think that only 'bad' programmers need a compiler to tell them that they are putting an in into a string var :-)

jnutter

Whoo! Quite the debate I've spawned here...

Anyhow, Vincent - I agree PostGreSQL is indeed a more robust database. However, my app is due to be finished by next week so learning PostGreSQL between now and then is not much of an option I'm afraid 😉

Thanks for the tip on the FULLTEXT searches - I'll definately look into that.

As for XML, I have yet to find much in XML that goes beyond simple 'convenience' and prefer to work with my own system. A little extra work is fine by me so long as my end result app is as "simple" as possible - the fact that the other "pure HTML" developers on my team can still read all my code and are not intimidated by it is a BIG plus in my books. Introducing XML would just confuse the situation ever more for them.

As far as transaction roll-backs are concerned: yes I could see that in a shopping-cart scenario they would be almost a necessity. However, my application is not a shopping cart - it is (primarily) a read-only information resource so the point is moot.

The lack of sub-queries in MySQL is a serious problem as far as performance goes, there is no doubt about that.

Vincent: does PostGreSQL support sub-queries?

On that note, learning my way around PostGreSQL is the next thing on my list of things to learn for sure. As far as MSSQL goes, I have to learn the basics of that to build my next project (clients requirement - not mine). After that project, I hope to never have to touch it again. I prefer Open Source over anything produced by Microsoft any day of the week.

nutter

[deleted]

Debates are cool! :-)

XML has it's uses, but I hate the way it's presented as god's gift to the internet. It's really nothing new.

Actually, the lack of sub-queries is not neccesarily a performance hit.
Not being able to sub-query makes you very awar of just how often you reall need to run a query. It's all too easy to build queries like
SELECT * FROM table WHERE id in (SELECT id FROM table2 WHERE this=(select that FROM table4 where t=1))
cute, compact, but does represent 3 queries.

Not being able to do this forces you to find other ways of doing it, to be more creative than just adding another query.
That really helps sometimes.

And yes, PgSQL does support sub-queries.
Interesting trivia: PostgreSQL is the great-great-grandparent of INFORMIX (that cute database company that IBM bought for a mere i billion dollars)
The setups of the two still have many similarities.

<unix_nerd_talk>
As far as beating MS-SQL goes, I'd just want to add that MS-SQL runs on windows platforms.. that should be enough reason to dislike it :-)
</unix_nerd_talk>

neuromancer

Select is a bad example of using subqueries. It is exelent for DELETE/UPDATE.

Also, I think XML should only be used for passing dynamic data into static stored procedures (We tried using it in other places and it just didn't seem to be of any help to us).

As for preferring windows to unix, right tool for the right job 🙂 I have a Win2K and Unix workstation side by side at work so I can take advantage of both platforms.