query vs logic speed

[mxb7642]

I have a website which uses some fairly large tables ( > 300,000 rows ). As you can imagine accessing these tables can often be very slow. I am attempting speed up my sql and was wondering if, using php, it is faster to perform an sql query or approximately 10 lines of logic?

What I am trying to do is, instead of querying the table thousands of times and performing my calculation on each small chunk, I would like to try to get the whole table at once, sort it by the ID's and just iterate over it and perform the calculations I need.

Would this speed things up? how much if any? and is it worth the effort?

Thanks for all the help.

[guelphdad]

300,000 rows is not significant for mysql. you would most likely be faster doing your calculations directly in the database.

note sure why you think you'd need to query the table thousands of times though. perhaps you could explain what you are doing by way of example.

[CFMaBiSmAd]

The best answer to the question is dependent on what the data is and what processing you are doing to it.

The answer could range from doing the calculations using built in SQL functions in the query on a properly indexed and properly designed table to caching just the data you need, either in memory, a flat-file, or a separate database table and iterating over it using PHP logic.

If you have a web page that is querying a table a thousand times, perhaps something can be done with the table design to help or the query should be changed to return all the data you use on the page at one time instead.

In any case, provide more information about what you are doing and some examples of the data, the queries, the calculations, and the output of the data on the web page.

Edit: Last two paragraphs are longer versions of what guelphdad posted above.

[mxb7642]

I'm pretty new to sql so upto now, the only table lookups i've had have been straight selects with no aggregate functions. Since i posted this, i've actually learned of these (SUM, COUNT, MIN, MAX) and they appear to be very helpful. Are there any others you could suggest that are useful? or any tutorials that might list them and how they work?

Thank you

[guelphdad]

the functions are covered in the manual. you can refer to chapter 3 in the manual as well for a brief tutorial on using mysql, some examples of which use those functions.

more important than the use of basic functions:

learn how to use JOINS properly, many people don't. Use ON clauses for clarity.

learn how to use GROUP BY properly, many people make errors in using it. There is an article in the resources thread at the top of the forum that is a good place. understand though that it is a pretty heavy read for a new user.


know how to use DISTINCT, it is not a function and is applied to every column in your select statment.

many people use something like this:

select distinct(columna), columnb, columnc

and then wonder why their data still has "duplicates".


know how to use the HAVING clause.

And lastly, but most importantly, know that your front end scripting language should be used, for the most part, for displaying your data, once mysql has done the heavy lifting of the data for you.

Oh and on that note, don't do a mysql query inside a loop of results from another sql query. For instance don't query one table to get the makes of cars and then run a loop in php where inside that loop you query the database again for each make. get both the make and model from your tables using a join.

[mxb7642]

Thank you!

Oh and on that note, don't do a mysql query inside a loop of results from another sql query. For instance don't query one table to get the makes of cars and then run a loop in php where inside that loop you query the database again for each make. get both the make and model from your tables using a join.

Thats exactly what i was doing :(. Its fixed now and hundreds of times faster :). I'll check out all that other stuff you mentioned too.

[mxb7642]

Im trying to use INNER JOINS, but now i'm getting errors. I had it working with other statements so this one makes me wonder if maybe there is a limit to the number of inner joins you can do in 1 query? Here is my code:
and the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.mobZone ) INNER JOIN zone ON mob.mobZone = zone.zoneID WHERE guild.serverID = 1' at line 2

Is there something wrong with my code?

( And on a side note, is using the function MAX() in the WHERE clause ok? )

solved :)

[guelphdad]

show your entire query. no there is no limit to the number of joins you can use.