BIG issue but small question

[karthikeyan_coder]

I have 334,164 rows in a table. I guess it will be increased to 7-8 digits soon.

 

I have some db operations.

 

I need to take 120,200 random rows from that table. so i use...

$num = rand(120,200);

$query = "SELECT * FROM `links` ORDER BY RAND() LIMIT 1,$num";

 

&

 

I will delete some rows based on some range...

Lets say..

ID ------ LINK

1  -------  href=http://subdomain.domainname.ext/somepage-asdf.ext>keyword</a>

5000  -------  href=http://subdomain2.domainname.ext/somepage-dafsdf.ext>keyword</a>

60000  ------- href=http://subdomain3.domainname.ext/somepage-ewrw.ext>keyword</a>

60001  ------- href=http://subdomain.domainname2.ext/somepage-gfvf.ext>keyword</a>

300300  ------- href=http://subdomain.domainname3.ext/somepage-gfvf.ext>keyword</a>

 

I have to delete all the rows which contains $delete_this_domain

 

$delete_this_domain may be "domainname.ext" , "domainname2.ext"

Just main domain part...

I may have to delete 60000 rows in a query.. is MySQL can work smoothly? and please tell me a better query for deleting those rows.

 

 

 

[trq]

Your question is simply too vague to understand. You want to delete all rows where a field = domainname.ext?

 

DELETE FROM links WHERE fld = 'domainname.ext';

 

Done.

[Ninjakreborn]

Yes, based on the description of your problem, I think thorpe's code will solve your issue.

If you have to have a random one, then put the domains in an array, and then randomly pick one of the domains from the array.

[Barand]

1 ) see http://www.phpfreaks.com/forums/index.php/topic,125759.0.html

 

2 ) DELETE FROM tablename WHERE INSTR(link, '$delete_this_link') > 0

[karthikeyan_coder]

@thorpe.. yes based on field

@biz man.. How long mysql will be stable and fast? Coz i will have 7 to 8 digits length number of rows soon... Some Millions.. and the speed of the execution of delete query with 3000000 rows.. This is my doubt..

[Barand]

Should've added

 

or

 

DELETE FROM tablename WHERE link LIKE '%$delete_this_link%'

[Ninjakreborn]

Normally by what I have read, mysql database' doesn't really matter on the number of fields, it's how often they are called.

You could have a million fields, with millions of row's of data, but if the site is rarely visited, then you don't normally have a problem.

I think if your site is getting decent traffic, with that many row's.

My motto lately when it comes to mysql is.

First use it, when you start encountering problems then do some "quick fixes" where needed, like making the number of allowed open connections increased, and debugging the database, and optimizing the database, just basic things like that.

When it gets to the point where you can't handle it, and there are problems around all over the place, then get a separate server entirely to handles your database, and allocate all it's resources to just that.  This will solve your problems for a long time to come, they call it a "cluster".  From here, if you still have problems you can always go to PostGreSQL, which isn't as fast with less user's, but it can handle almost double what mysql can.  If you are ever getting where you are still having problems, then you should start looking at oracle, or other big database systems, and see what you can come up with.

Based on what you have described, with your current situation then you shouldn't currently have any problems with it acting up.

[AndyB]

My motto lately when it comes to mysql is.

First use it, when you start encountering problems then do some "quick fixes" where needed, like making the number of allowed open connections increased, and debugging the database, and optimizing the database, just basic things like that.

When it gets to the point where you can't handle it, and there are problems around all over the place, then get a separate server entirely to handles your database, ...

 

Good grief!  How about designing it right from the start?  Just like a house - plan it before you build it.

[ToonMariner]

No no no - thats not how to crate large, scalable, portable web based applications at all.

 

Andy - how dare you suggest such tripe...

[trq]

First use it, when you start encountering problems then do some "quick fixes" where needed

 

I'm sure freelancebusinessman's clients would love reading this stuff!

[Ninjakreborn]

I think these forums are for helping people not criticizing people.

You seem to think what I said was wrong, but that Is what I have been told.

Fact - mysql is more reliable with smaller user's, but get's bogged down with lot's of user's.

Fact - PostGreSql is not AS reliable as mysql, but can handle large groups of user's better.

Fact - Oracle is one of the most reliable with very high numbers, and that's why it's the most expensive.

Fact - If you put Mysql on it's on server and "cluster the database", not exactly sure what that entails but I have had that suggested, then you get better performance than mysql on a shared server.

 

Those are all facts, if someone can dispute one of them, please do so with proof.

As far as what I said, my general theory is

Start with Mysql - CLuster if needed - Go to PostGreSql after that if needed - Oracle if needed.

That sounds like something sound, you have to start somewhere and very few will have the money for oracle right off.

Based on what I have said no clients would think any differently of me.

If someone has something to say about what I mentioned about, please point out what part they think I am wrong on, and tell me in what way's I am wrong.

Allow me to learn from my mistakes instead of criticizing me for them.

[Thierry]

First use it, when you start encountering problems then do some "quick fixes" where needed

 

I'm sure freelancebusinessman's clients would love reading this stuff!

 

Yeh cus planning is for pussies

[Ninjakreborn]

Well I don't know about all of that.

I think planning is very important, I end up trying to plan out everything.

When I mentioned that, I didn't mean skip planning.

I try to plan for my database structure more than anything because ALL of the programming generally revolves around the structure of the database.  However even the best laid out database structure can get bogged down with a lot of hit's.  It's bound to happen on any site that get's to a certain point.  Look at PHPfreaks.com, they started out great, then just about 6 months ago they started having lot's of problems with there databases.  I mean error's all over the place, connection areas, and a lot of other things that were causing problems.

Now they don't ever have problems, so they had to have done SOMETHING to stop those errors, the traffic only increased since then, and there is no way they could have gotten away with not doing something.  Now there are hardly (if ever) any errors related to the database.

It had to of been because of "fixes" they did to allow the database to hold more, I doubt they changed database systems or it would have been down for almost 3-4 day's.

 

That's what I don't get too, you said plan it before you build it.  That's pretty much what I meant, but you can plan out how many users you are going to eventually have.  Not everyone can jump to oracle first thing, some people have to start with mysql, and for me, for almost all situations it's the best choice.  unless I have a site I created that I am in charge of that ends up having mysql errors because there are too many user's coming in than it can handle.

 

Also based on your other question, deleting 30,000 row's from a database will take very little time for mysql if you are just running a delete query.

[Nameless12]

I think these forums are for helping people not criticizing people.

You seem to think what I said was wrong, but that Is what I have been told.

Fact - mysql is more reliable with smaller user's, but get's bogged down with lot's of user's.

Fact - PostGreSql is not AS reliable as mysql, but can handle large groups of user's better.

Fact - Oracle is one of the most reliable with very high numbers, and that's why it's the most expensive.

Fact - If you put Mysql on it's on server and "cluster the database", not exactly sure what that entails but I have had that suggested, then you get better performance than mysql on a shared server.

 

Those are all facts, if someone can dispute one of them, please do so with proof.

As far as what I said, my general theory is

Start with Mysql - CLuster if needed - Go to PostGreSql after that if needed - Oracle if needed.

That sounds like something sound, you have to start somewhere and very few will have the money for oracle right off.

Based on what I have said no clients would think any differently of me.

If someone has something to say about what I mentioned about, please point out what part they think I am wrong on, and tell me in what way's I am wrong.

Allow me to learn from my mistakes instead of criticizing me for them.

 

So much is wrong with what you said, I really dont know where to start, postgres has some serious advantages over mysql... You so have it wrong but here is a small quote from the above

You seem to think what I said was wrong, but that Is what I have been told.

 

Like you said, that is what you have been told. Did you ask for proof by the person who told you it in the same way you are asking for proof to refute the claim?? Somehow I doubt you asked for proof....

 

I try to plan for my database structure more than anything because ALL of the programming generally revolves around the structure of the database.

 

Also you said all of the programming revolves around the database I find this to be complete and utter crap. It depends on the type of programming you are doing. I think that it is only the beginners who use a database for "ALL programming" as that is the only kind of storage they are familiar with so they just do what they are familiar with.

 

 

[john010117]

Ok people. Let's settle down and get back on topic. Don't let a fight break out.

[Ninjakreborn]

Actually that was all I was waiting for.

Ok about the programming, I meant that a little differently.

You are right about that.

Most of these things I have heard from other people, and never asked for proof.  So can you show me proof that postGreSql is better than Mysql and in what way's.  So out of those 4 things I "thought" where fact can you help me differentiate which one's are fact, and which one's I created just off of other peoples "opinions".

[Nameless12]

Fact - mysql is more reliable with smaller user's, but get's bogged down with lot's of user's.

Fact - PostGreSql is not AS reliable as mysql, but can handle large groups of user's better.

Fact - Oracle is one of the most reliable with very high numbers, and that's why it's the most expensive.

Fact - If you put Mysql on it's on server and "cluster the database", not exactly sure what that entails but I have had that suggested, then you get better performance than mysql on a shared server.

 

in answer to your question I think you need to understand where these databases come from.

 

Mysql started off trying to make a very fast database that was easy for the masses to use where as postgres started off trying to make a nice solid relational database. At the moment postgres is the worlds most advanced open source database and mysql is the worlds most popular open source database.

 

I think the fact postgres has gone for completeness as opposed to leaving stuff out for the sake of "being faster" is what makes it a more mature database. It wasnt until version 5 that mysql added many of the features that have been the norm in many other databases and these features are to do with data integrity. The way I see it postgres has had these features for a considerably longer amount of time.

 

And as a personal note it annoys me how mysql has so many table types, postgres by comparison has one table type.

 

As for oracle I have not used it so I cannot really comment to much about it, but I think oracle just has a different target audience than both mysql and pgsql.

 

Each database is unique and its kind of stupid to say one is better than another it depends on the kind of data you are going to store in the database as different databases have different strengths and weaknesses just imagine comparing sqlite to mysql, its just silly.

 

 

[Ninjakreborn]

Is there somewhere I can find like a list of some of the strengths/weaknesses of the different database systems (relational/non-relational).

I want to learn which one's are better for which purposes, and how to tell the differences.

Thanks for telling me this.  I want to find out more about it.

[Nameless12]

I cannot give you a definitive source, maybe someone else can. I have not looked up this kind of stuff in over a year so i am a little out of date.

 

to give a quick and generic summary

 

sqlite is good for configurations and times you want portability, it does not even have a username and password and fits on a disk.

 

mysql is good due to its popularity, and its popularity is due to its ease of use. This is both a plus and a negative as it is less sql compliant as a result of this

 

postgres is more sql compliant than mysql and has considerably better stored procedures.

 

there are probably other things but I tried to keep the summary as generic as possible. Also a lot of stuff depends on who is going to be maintaining the database as well as budget. And in my opinion mssql should of been on that list of yours too.

[Barand]

Borland's Interbase is a good, solid and mature product too

[Ninjakreborn]

hmm, I think based on what I learnt today.  I won't ever look at all database systems the same again, I will actually do research on the strengths/weaknesses of each and report what I find here, and reference urls.  Thanks.