1

I am writing backend code in PHP where there are client requests and I have to show them a collection of users based on priority amongst many thousand users.

My Problem is this: since the queries are too heavy requiring a lot of inner joins and comparisons, that will make the code very slow and the response will consume a lot of time.

Can you suggest a method by which I can accumulate all the users, perform all queries and calculations without affecting the request and response times?

jogojapan
  • 68,383
  • 11
  • 101
  • 131
Tani
  • 115
  • 2
  • 14
  • I don't understand the point of down-voting my question when i was simply asking for a suggestion and not a complete code – Tani Nov 29 '12 at 10:14
  • @Elby: I am searching on stored procedures but i am not sure about it thats why i am asking for suggestion – Tani Nov 29 '12 at 10:17

3 Answers3

1

Queries with a lot of JOINS and CONDITIONS will have an effect on performance but is very minimal, as long as you have a good database design (Database Normalization). What mostly affects the performance of your code is the size of the tables (number of records) used in the query, but still there are INDEXES (How to use Indexes) and other tools which will help in optimizing your query.

I personally suggest that you use Stored Procedures in your queries, which applies encapsulation. And as per implementation, will help to avoid SQL Injection.

Community
  • 1
  • 1
KaeL
  • 3,639
  • 2
  • 28
  • 56
0

You can try a technique called Materialized views, i.e. doing precomputations into some tables containing agregates and using the agregates to speed up your online query. On the other hand, if the data you're basing your computation on, is changing often, this technique won't be of much help.

Look here for some more details on the subject: http://www.fromdual.com/mysql-materialized-views

px1mp
  • 5,212
  • 1
  • 18
  • 10
  • Thankyou for the response. What i am getting is creating views by implementing procedures. Am i right? – Tani Nov 29 '12 at 10:28
  • You don't have to implement procedures necesarily. You just decompose your query into several steps and store the intermediate results in a new table. This way, you save time, as you do much less joins on the time critical query. As some other user below notes, make sure, that you use indexes properly, this can spead up queries by orders of magnitude. – px1mp Nov 29 '12 at 15:08
0

Use Mysql Views to avoid complex queries and long time!

vishal shah
  • 212
  • 1
  • 3
  • 15
  • Basicaly, views are not materialized, i.e. they only exist as a kind of relation and the underlying table joins are calculated each time, you reference a view. Thus it's necesary to use those "materialized views", i.e. precalculated intermediate tables. – px1mp Nov 29 '12 at 15:12
  • But by using views, we can call required queries easily and quickly to get the data without calling those complex queries. – vishal shah Nov 30 '12 at 05:57
  • Indeed, but all the queries used to get the view are still done on the background, so you will avoid the complexity of constructing the queries, but you wont gain any query performance speed-up in this process. – px1mp Dec 01 '12 at 10:31