MySQL queries - how expensive are they really?

views:

145

answers:

+7 Q:

MySQL queries - how expensive are they really?

I've heard that mysql queries are very expensive, and that you should avoid at all costs making too many of them.

I'm developing a site that will be used by quite a few people, and I'm wondering:

How expensive are mysql queries actually? If I have 400,000 people in my database, how expensive is it to query it for one of them?

How close attention do I need to pay that I don't make too many queries per client request?

Edit: TomTom couldn't be more correct with his answer. According to perormance tests on the sites linked to in the comments (and a couple sites I found through a "mysql queries per second" search), I won't be encountering problems for quite a while - mysql can do thousands of queries per second with a remote server.

+11 A:

Just ignore it. Seriously. If you need data, you need data. Make sure your queries are reasonable fast (i.e. proper indices). Get a proper server for handling the load. Use output caching as makes sense, start caching data once you know where you actually HAVE a problem.

TomTom 2010-03-18 11:59:10

That's reassuring, thanks :) - one thing though, can you give an example of proper indices? Not exactly sure what you're referring to.

Cam 2010-03-18 12:00:17

If you always select i.e. a user by his username then you should set an index on the username column. If you select by user id then set an index on the user id column (probably there is already a primary key on it). In short: Set indexes on columns that appear most frequently in your where-condition and always check with "EXPLAIN SELECT ..." if the index is used.

dbemerlin 2010-03-18 12:06:26

@incrediman: Take a look at http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html , http://dev.mysql.com/doc/refman/5.1/en/using-explain.html and maybe http://www.mysqlperformanceblog.com/ or http://jan.kneschke.de/projects/mysql/

VolkerK 2010-03-18 12:07:08

@dbmerlin Ah, yeah I figured that's what was meant. Makes sense; common database good practices. EXPLAIN SELECT is new to me though, looks interesting.

Cam 2010-03-18 12:07:39

+1 A:

It depends on

a) Your traffic levels b) Your server c) How expensive your queries are

For example, as a rule of thumb, I never go over 15 queries a page. However I allow for atleast 6-7 to be expensive (1-2 joins).

Anything thats only changes very rarely (movie descriptions as an example) I would cache. Then in administration, delete that cache file when that movie is updated.

However I have had sites that have 80 queries (back in the day) and had 200+ visitors constantly refreshing with no issue but they were all very light.

I don't think there's any real solid rule people will give or you should abide by.

azz0r 2010-03-18 12:14:58

Thanks, +1 for the numbers, that's very reassuring. I have about 8-10 queries per page max, with most pages only using a couple queries. None of my queries are really that expensive at all either - they're mostly simple selects and inserts (although there is one SUM, which I guess is kind of expensive, except for the fact it's only summing about 250 rows per pageview).

Cam 2010-03-18 13:36:25

+1 A:

As long as your queries are precise and written as efficient as possible, mySQL is made to be able to perform these actions.

If you say have 400,000 users though, and your noticing performance issues you should be making enough money off of these users to upgrade your server anyways :)

Mike Silvis 2010-03-18 12:51:54

ansaurus

tags:

views:

answers:

MySQL queries - how expensive are they really?

related questions