How big one MySQL database can be?

[linux_19]

Hello all,
I am curious about how many records one MySQL database can handle?I am tryin to make a search engine with over 500 000 records in one single table

But as you will notice on the first search it takes up to 5-6 seconds to return results then every other search after the first is very fast...is it about the large MySQL database,or this has nothing to do,and it's up to my PHP scripts?
Thanks alot in advance!

[edited by: jatar_k at 11:38 pm (utc) on June 27, 2006]
[edit reason] no urls thanks [/edit]

[eelixduppy]

You can find some information regarding your question at this thread [webmasterworld.com]. Good luck!

[jmccormac]

First off, I don't know what level your programming skills are at. So it might be a good thing to use a real search engine program if you have to search a large website or a large number of sites. PHP with MySQL is great for limited searches but genuine search engine programs offer a lot.

Take a look at [mnogosearch.org...] - it is an Open Source search engine that runs well with Linux and MySQL. There are other such as Nutch and Datapark [dataparksearch.org...] which are also worth looking at.

One reason for the subsequent searches being faster is that the MySQL tables are loaded into RAM when the first query is executed. They are still in RAM when the other searches are executed. One of the rules with databases is that the more RAM you have, the better the database will run because the data will be resident in RAM and will not have to be continually swapped between harddrive and RAM.

I use MySQL with very large 100G+ databases dealing with domain name tracking. Some of the tables have around 4 million rows.

Regards...jmcc

[Scally_Ally]

i have a really large database (700,000 records) that i am managing on a site at the moment.. i had a problem with searching times on the database for logins etc.

One way that helps to speed this up is on the column that you are going to do most of your searching on create an index and a unique identifier. this made the search results so much quicker.

Also try using the 'EXPLAIN' comand in MySQL, for example 'EXPLAIN SELECT * FROM table WHERE ID1 = ID2'.. This will then give you details of how 'good' your query is. Have a look at this link..

[databasejournal.com...]