MySQL speed question (12, permasaged)

1 Name: #!/usr/bin/anonymous : 2009-08-09 08:04 ID:5FfRIDHu

I was wondering, when you have a large set of data in a MySQL table, is it faster to find the data you want with MySQL's Select function, or by dumping the whole table into a PHP array and searching through that?

Basically, which works faster with lots of data, PHP or MySQL?

2 Name: #!/usr/bin/anonymous : 2009-08-09 15:03 ID:Heaven

MySQL.

3 Name: #!/usr/bin/anonymous : 2009-08-10 01:05 ID:Heaven

This is why database people hate PHP programmers.

4 Name: #!/usr/bin/anonymous : 2009-08-10 13:36 ID:Heaven

I guess I should be offering an explanation as to why >>2 is correct, so here it goes.

MySQL is a database thing. It's written in C and it offers an API (in C), that you use to store and retrieve data. It's pretty damn fast, faster than anything you or some other individual alone would be able to implement. So, other languages make use of this API by integrating the C functions et cetera to their interface. Since there's more work to be done in runtime, using functions from those languages is going to be a tad slower, but not enough to be noticable in a small/medium scale project.

You'll find the C api documentation here,
http://dev.mysql.com/doc/refman/6.0/en/c.html

As for PHP, you already know this I hope, but here's the docs
http://php.net/manual/en/book.mysql.php

5 Name: #!/usr/bin/anonymous : 2009-08-10 13:38 ID:Heaven

>>4
Ah yeah, but converting the data retrieved to some native structure of the language you are using (ie an array), and searching... is a million times slower.

Not just the conversion is going to be awfully slow the bigger your data is, but searching might take minutes. MySQL takes proper care of the right data structures and algorithms for your data.

6 Name: #!/usr/bin/anonymous : 2009-08-12 01:31 ID:OJY4A1Y5

>>4-5 are both failing to use the two important words that PHP programmers in general seem to be oblivious to: query optimization. If you write your queries properly, the database can cut down something that you might implement in three ugly nested loops manipulating two nested sets of associative arrays, as one btree lookup and a string comparison. Simply put, the database knows much more about the data than the view does.

However, in order to properly make use of this fact, you still need to construct your databases non-stupidly. That's where all those principles of normalizing data and setting up indexes and so on really make a difference between a big box that you throw all your paperwork into haphazardly and then sort them one at a time when trying to find what you want, versus a well-organized filing cabinet with labeled hanging folders in alphabetical order.

7 Name: #!/usr/bin/anonymous : 2009-09-03 10:23 ID:p65GPQ8h

>>6
Listen to this man.
And brush up your SQL.

8 Name: #!/usr/bin/anonymous : 2009-09-10 10:11 ID:MFuI8/mg

and you're all forgetting to remind OP that premature optimization is the root of all evil. are you sure it's the table lookup that's taking most of your time?

9 Name: #!/usr/bin/anonymous : 2009-09-11 00:24 ID:JQsxk87S

>>6

> MySQL
> query optimization

HAHHAHAHAHAHAH

10 Name: #!/usr/bin/anonymous : 2009-09-12 01:13 ID:Heaven

>>9
I never once said "MySQL" in >>6. However, it does have a (pithy) query optimizer, which nonetheless still beats what you could do in PHP alone.

11 Name: #!/usr/bin/anonymous : 2009-09-21 23:04 ID:LIuXLqkK

everyone knows the only way to optimize is assembly, you must write your cgis in assembly php is toy language enjoy your sql injection

12 Post deleted.

Return Entire thread Last 50 posts
This thread has been closed. You cannot post in this thread any longer.