Fastest Database For Search

[The Little Guy]

Anyone know what the fastest database is when you want to search and join tables that contain about 4 million entries...

 

 

 

[premiso]

http://cassandra.apache.org/

 

I love Cassandra!

 

But you may be looking for something along the lines of: http://sphinxsearch.com/

[The Little Guy]

Do either of those work well with PHP?

[Philip]

I <3 sphinx. I've seen it work wonders.

[PugJr]

I defintely wouldn't use MySQL. I was so fed up with it that I just made a generic one myself to suit my needs. MySQL for whatever reason doesn't index properly. You have 10 million rows. It takes longer to get 1 from that than a table of 10. I did not use any undefined data types so whatever, done with it. Also 1 dimensional indexs cost data. 1D indexs should not cost any data.

 

I haven't tried any other DBs though.

[hackalive]

MySQL it is used by Google for their AdWords system once they saw Oracle was tooo slow

[The Little Guy]

MySQL it is used by Google for their AdWords system once they saw Oracle was tooo slow

 

Google uses BigTable

[Daniel0]

MySQL it is used by Google for their AdWords system once they saw Oracle was tooo slow

 

Google uses BigTable

 

Not for everything.

 

http://www.mysql.com/customers/view/?id=555

[hackalive]

MySQL it is used by Google for their AdWords system once they saw Oracle was tooo slow

 

Google uses BigTable

 

Wrong, for AdWords as I said Google use MySQL and they are using it more and more

[trq]

MySQL it is used by Google for their AdWords system once they saw Oracle was tooo slow

 

Google uses BigTable

 

Wrong, for AdWords as I said Google use MySQL and they are using it more and more

 

Care to cite your source?

[Mchl]

The database is only as good as it's administrators are. If you don't know how to use your tools properly, you can bog down anything whether it's MySQL or Oracle or MSSQL or whatever.

[premiso]

The database is only as good as it's administrators are. If you don't know how to use your tools properly, you can bog down anything whether it's MySQL or Oracle or MSSQL or whatever.

 

WoW - Words of Wisdom

[PugJr]

Mchl, databases decide how smart the adminstrators are going to be. If you have a database where everything is strict and logical, and then you have the database that has bogus columns (Undefined), you'd expect more difficulties with the adminstrator in the latter (Which happens to be MySQL). Why in the world does MySQL have an undefined data type? How can you properly index that? You can't. To anyone who thinks you can I will explain how you can't:

 

You have an undefined character row.

AAA

BBBB

CCCC

 

Now, what can I do to assume where row id 2 is? You simply can't. If row was defined though...

 

AAA(Blank)(Blank)

BBBB(Blank)

CCCCC

I know it starts exactly 10 characters from the left but this consumes 5 bytes per row, as the first example only consumed a total of 12 characters vs the 15.

 

As well there are character limits for certain datatypes that you need. Take for example Char. Char limits to 255. Okay, well what happens if I want more than 255 characters? I've looked into text/varchar type, but that is undefined. Undefined doesn't work.

 

 

Although I'm not an expert in MySQL but even still I have wasted a bit of time trying to figure things out, such as I couldn't find out how to index without consuming data. Theres the 'index' button, but it consumes data (I did index by id). So perhaps MySQL is really great, but if it can do all I want it to, why does it have to be so confusing and give non-logical options? (Stated previously).

 

I do have to give it to MySQL to being newbie friendly. A 5 year old could make a database in MySQL. It is very effective when consuming alot of RAM, such as being able to 3D index a 12 million row table, and load rows as fast as .3 seconds but this isn't perfect and as the OP wants 'fastest database for search', MySQL surely doesn't win.

[Daniel0]

What the hell are you talking about? None of your post makes sense.

 

You have an undefined character row.

AAA

BBBB

CCCC

 

Now, what can I do to assume where row id 2 is? You simply can't. If row was defined though...

 

The concept of being number 2 only exists if you choose an ordering beforehand. Or do you mean on the disk? You don't unless you choose CHAR as your datatype.

 

AAA(Blank)(Blank)

BBBB(Blank)

CCCCC

I know it starts exactly 10 characters from the left but this consumes 5 bytes per row, as the first example only consumed a total of 12 characters vs the 15.

 

Duh, yes. Being able to access information quickly has the tradeoff that it takes up more space. What did you expect?

 

As well there are character limits for certain datatypes that you need. Take for example Char. Char limits to 255. Okay, well what happens if I want more than 255 characters? I've looked into text/varchar type, but that is undefined. Undefined doesn't work.

 

What do you mean with undefined? What's undefined? VARCHAR is exactly the same as CHAR except it has variable ("VAR") instead of fixed length. This means 255. TEXT can store 2¹⁶.

 

Although I'm not an expert in MySQL but even still I have wasted a bit of time trying to figure things out, such as I couldn't find out how to index without consuming data.

 

Of course indexes take up space. How exactly do you imagine you would build an index without taking up space? Again, it's a tradeoff. A typically used data structure for building an index could be a B+ tree. This allows searching in O(log n), which is faster than just searching through the table linearly. However, maintaining an index means slower insert times (you'll have to update the index) and a larger disk space requirement (the index needs to be stored somewhere).

 

The power of a DBMS is specifically that it has some metadata that makes it able to figure out how to get the information as fast as possible. If you want to consume the minimal amount of space then store it in a flat file with one row per line and no metadata.

 

Besides, hard disks are really cheap, so the disk space consumption is the least of your worries.

[PugJr]

What is an undefined data type? An undefined data type is a data without a precise data amount. Varchar, text, etc.

 

Of course I'm talking about on disk. I didn't even know people put databases on RAM. (Guess I'm more ignorant than I thought, but facts are facts even if I am unaware of this one.) Choosing an ordering beforehand is logical. Perhaps my vocabulary is confused as I thought that was pretty much what an index is. I suppose then why wouldn't MySQL allow an order beforehand option? (I just googled it and didn't find anything.)

 

You say "Duh, yes. Being able to access information quickly has the tradeoff that it takes up more space. What did you expect?" and then say "Besides, hard disks are really cheap, so the disk space consumption is the least of your worries.". So why doesn't MySQL have a defined data type of greater than 255 when harddisk space is more than enough for most databases.

 

Varchar and other undefined datatypes can bring your database speed to a snail for a little price of a bit more harddisk space. In my previous post I stated a 3D index that loaded at .3 seconds with 12 million rows. That doesn't even have undefined datatype. Here, I'll add one though. (Okay, my computer got faster or something, because the .3 seconds was based on from a couple weeks ago and I did not recently check, this is what the results are as of now:)

 

Without Varchar(5) and Char(5): .13 seconds

With Varchar(5): .18 seconds

With Char(5): .13 seconds (Shocker!!!)

 

My point being is that text and varchar are worthless as they can't be properly indexed (On harddisk). They might as well not exist in MySQL if you want a proper DB. So that limits you to /only/ Char which is limited to 255. Unless you scoff at an additional .05 seconds per request...You could argue "Well jeez, that .05 seconds isn't free! It costs a massive 60 MB!!!". Or you might think I'm just a cheat liar who is making numbers up. Why don't you try it and tell me your results since clearly I have no clue at all what I'm talking about? (Make sure the database is real big to see the difference. Bigger the database row count, the bigger the difference in speed.)

 

Indexes don't require data. Heres an example.

 

AAA

BBB

CCC

DDD

EFD

SDF

ABC

 

That right there is indexed. All 21 characters (Pretend the spaces between them don't exist as its for the purpose of display so really its: AAABBBCCC...). What is row 0? It is 0 characters to the right and 3 characters long. AAA. What is row 5? It is (row id) * (length) which is 5 * 3 so 15 characters to the right  and 3 characters long. which gives me 'SDF'. That right there is index without size and doing it that way is considerably faster than MYSQL ever did for me. If a DB was properly indexed like that, no matter how big the table is, 100 million or 1, it will keep giving me consistent results of very small query time. (Until the harddisk breaks) MySQL goes up and up and up as more rows are added.

 

What do you mean by "The power of a DBMS is specifically that it has some metadata that makes it able to figure out how to get the information as fast as possible. If you want to consume the minimal amount of space then store it in a flat file with one row per line and no metadata."? Clearly MySQLs DBMS is awful if it takes .13 seconds to query something OR I just have no clue what the heck I'm doing with MySQL (Which sure could be it.). If something is properly indexed, it shouldn't take long at all and .13 seconds is a very, very, long time to query a row.

 

Also MySQL is a RAM hog but MySQL gives a longer life to harddisks (Or I would hope so, I can't actually prove any of that but it makes sense.). Currently my server is using about 1GB of ram. Thankfully I had a spare 512 MB RAM stick laying around. (Total to 1.5GB of RAM).

 

Ultimately the point is, like the topic said, fastest database for search is the question, and I can use my own DB (Which has flaws to it as well), you use MySQL, and we both have a huge database (Atleast 10 million or lets make it 1 billion...) and we then query some arbitrary ID. I would not be shocked at all if mine wins every time. (Even if your computer is faster which it surely is.). My DB is surely not the fastest in the world (Or even close), but its faster than MySQL. As well, with 1 billion rows, you would need 4 bytes per ID, which means MYSQL database would be 4 GBs greater than mine just in index.

 

But like I said previously, maybe I have no clue about how to work a MySQL DB (But I wonder how many do then...).

[PugJr]

MySQL:

 

Showing rows 0 - 0 (1 total, Query took 0.0276 sec)

SELECT *

FROM `test2`

WHERE `id` =56555

LIMIT 0 , 30

 

0.02 seconds...For that? Really? This is the table settings:

 

-- phpMyAdmin SQL Dump

-- version 3.2.2.1deb1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Apr 05, 2010 at 06:44 PM

-- Server version: 5.1.37

-- PHP Version: 5.2.10-2ubuntu6.3

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

 

-- --------------------------------------------------------

 

--

-- Table structure for table `test2`

--

 

CREATE TABLE IF NOT EXISTS `test2` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `number` int(11) NOT NULL,

  KEY `id` (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=905135 ;

 

 

Now please do tell me if there is something wrong with my settings, but thats a pretty basic table. ID that auto-increments and a random number per row. I do realize that its KEY and not INDEX, but with auto increment it has to be.

 

My database has the same values as the one above (Well, except the index is assumed, so I only have a number column, and my number column is 4 bytes as well, random like I did for MySQL. My DB only has 200k rows VS MySQL with 900k, but I was getting bored of waiting for the DB to fill up to 800k. It does not matter the size in a proper database. I could have a million rows and still have the same result and if you don't believe me, I'll go through with it.)

 

MySQL - 13333 writes a second.

My DB -  1250 writes a second. (I know its not very good)

 

1 query below...

MySQL - 0.02

My DB - 0.0004

 

Okay, so I'll be honest. I rigged it here. The time MySQL called for the row, it wasn't cached. But what happens once you run out of RAM? Game over. Heres the cached version with 1 query below:

 

MySQL - 0.0002

My DB - 0.0004

 

Okay, so fine, MySQL is faster, if it has RAM. I spent about 3 days working on my DB and its no where near maximum optimization. I'm sure people who made MySQL have gone over it and over again making sure its at the fastest it can be. My whole point here isn't that my DB is good or anything along those lines, but that if mine is faster than MySQL (Assuming no RAM, which would happen if you had a 100million row table for instance), MySQL can't be very good. Now it might raise the question why its so popular, and that can be answered by that its easy. Very, very, easy.

 

Anyways, I'm not even sure if the fastest database you want is in PHP or not, but if you can choose any language, PHP is defintely not your best choice. This language is intended for ease, not speed.

[Daniel0]

Sorry, but you obviously don't understand what an index is or let alone how relational databases work. Go read a book about databases, it's too much to go over in a forum topic.

 

How does your database handle ACID, transactions, locking, integrity constraints. How about "simple" things like table joins or aggregate functions? Or how about user authorization on database/table/column level?

 

There is more to a DBMS than raw speed on simple selection queries, and that is of course the reason why the world uses real databases like MySQL, Oracle, MS SQL, PostreSQL, etc.

[JonnoTheDev]

Just to add my two penneth worth. I would highly recommend Sphinx. Another option is Lucene, which is part of the Zend framework.

[Mchl]

For your example InnoDB could be faster. Also first time you query a table, there's an overhead related to opening the table and loading it into memory (which MySQL does, because it assumes you want it to do some serious work, and not lookup a single value ).

 

And MySQL might seem simple, but to use it efficiently is not necessarily easy. See here:

http://explainextended.com/2010/04/02/multiple-attributes-in-a-eav-table-group-by-vs-not-exists/

for example of how different ways of querying same data, can make a 90-fold difference.

 

Honestly, I found this article the day before yesterday, and before that I wouldn't even believe it is possible to do it that fast

[The Little Guy]

question...

 

First off, I will say that my table has 3.7 Million rows.

 

I am using INT(11) for my auto_increment, would speed increase if I used a MEDIUMINT(7) instead?

[Mchl]

Unlikely. To tell anything meaningful we would have to see table structure and queries you run on it.