Help in understanding server load in putting more work on php or on mysql

[brentman]

So I have a big table of about 30-40 columns. Some variables are quite long strings while others are simple tinyint(1) values.

 

Input into the code is a value or range of values or something that applies to each of the columns.

 

I need to find which ones are an applicable match to the user request and then load them into php.

 

Now I know variations can cause something to run more optimally one way or another but this system will get bogged down with lots of requests and lots of data, so any additional performance I give it now will help me down the road. I am just looking for general concepts to help, not specific code tweaking.

 

I have heard the typical rule is to query mysql as little as possible and do the hard lifting in php but I am not positive this is right.

 

Since I have to query anyways, should I do a mysql pull using a complex 'WHERE' to do most of the sorting. ie only get rows where a=1, b>5, c="http://google.com", d="3242342323kj4238237489023ejfjf3jrjf8jeifjdjf" ie long string, etc for all 30+columns?

 

OR I pull the whole DB and do all the sorting in php...

 

OR I do all the simple sorting in the MySQL query and some of the more complicated sorting in PHP (or vice versa)

 

Any ideas would help.

[ginerjm]

Whatever specific criteria you have for selecting the desired record or records s/b used in the query's Where clause.  No brainer.  Using non-specific arguments (ie, using 'Like') is something you will have to consider the merits of.  Obviously that kind of search is resource-consuming.  If the search for specific values can limit the results to a manageable number, then perhaps using an 'if' in your results-processing loop can reduce your final results further.  It all depends again on the size of your results and the size of your table (rows).

 

 As for sorting, if you can sort the results using the Order By clause, then of course you do it.  Again - no brainer. 

 

Now that you have limited the # of rows returned, your code can then do whatever manipulation is left to do while you are looping thru the results and preparing your output.

 

As an aside - I wouldn't count on users supplying you with the contents of a "long string" to be searched for in your table.  Users are notorious for not making correct input.  And of course you WILL sanitize the inputs your users are providing before running your query.  Using prepared queries here would also be recommended.

 

IMHO - your concern about a "big table" of 30-40 columns is not that much of one.  A 'big table' is one with many, many rows, ie, thousands of them.  So this entire question seems unnecessary, but it shows that you are thinking. 

[Jacques1]

Data processing is done by the database system. That's exactly what it's for.

 

What may look like “lots of data” to you is a joke for MySQL. It can easily process hundreds of columns and millions of rows. So let it do its job and don't try to outsmart it. If you experience performance issues, that's usually due to broken database design or missing indexes. The EXPLAIN statement will tell you what's wrong.

 

Of course there are special cases where the processing logic requires a full-blown programming language. But your example doesn't even come close to this.

[brentman]

I am expecting to hit 100k rows.

 

But I think that answered what I needed. I will try to do most of the data checking through the MySQL query, avoid 'like' operations, and not worry so much.

 

Unless you think I should be worried about 100k rows on about 30+ columns. It is also a user experience issue if it doesn't load quickly. 5 second wait isn't good for what I am doing.

 

Thanks!

Edited December 21, 2014 by brentman

[Jacques1]

If you're so worried, generate 100k test rows, try a bunch of queries and see for yourself. You'll realize that even “complex queries” are typically a matter of milliseconds (assuming that the database design, the indexes and the query itself are all sane).

 

Aren't you aware that MySQL is also used by big companies? They have thousands of rows per second, not total.

[brentman]

I am just concerned because I have seen some companies go down because of database issues that were not fixable.

 

True story: One company needed to complete a query automatically every second. They were easily able to do that for a year. On year two, it was talking almost a second so they made some changes and it worked better. By the end of the year, it was failing occasionally but each time it cost them approximately $1000. At that point, there was no way to fix how it worked short of a complete rebuild from scratch of the core functionality which was estimated to cost around a quarter of a million dollars and take 6 months. That cash was not available, especially since business would have had to cease operation for 6 months and thus the company is now gone.

 

So after seeing that I am a bit nervous about the life of my database and code logic.

 

Test rows I think will be a great idea. Thanks. Once I finish my my queries I will definitely try that!!!!

Edited December 21, 2014 by brentman

[kicken]

If you're worried about proper design, then post your database design here (either as create table statements or a diagram) and explain what you're trying to accomplish. Then we'd be able to tell you if there are potential flaws in the way you're storing the data.