CODEWITHSUNDEEP
phpmysql
lyborko
lyborko

Reputation: 2619

How to Requery a query?

consider "Query1", which is quite time consuming. "Query1" is not static, it depends on $language_id parameter, thats why I can not save it on the server. I would like to query this "Query1" with another query statement. I expect, that this should be fast. I see perhaps 2 ways

  1. $result = mysql_query('SELECT * FROM raw_data_tbl WHERE ((ID=$language_id) AND (age>13))');

    then what? here I want to take result and requery it with something like:

    $result2 = mysql_query('SELECT * FROM $result WHERE (Salary>1000)');

  2. Is it possible to create something like "on variable based" MYSQL query directly on the server side and pass somehow variable $language_id to it? The second query would query that query :-)

Thanks...

Upvotes: 1

Views: 1427

Answers (3)

vbence
vbence

Reputation: 20343

With the use of sub queries you can take advantage of MySQL's caching facilities.

SELECT * FROM raw_data_tbl WHERE (ID='eng') AND (age>13);

... and after this:

SELECT * FROM (SELECT * FROM raw_data_tbl WHERE (ID='eng') AND (age>13)) WHERE salary > 1000;

But this is only beneficial in some very rare circumstances.

With the right indexes your query will run fast enough without the need of trickery. In your case:

CREATE INDEX filter1 ON raw_data_tbl (ID, age, salary);

Upvotes: 2

Slava
Slava

Reputation: 2050

Although the best solution would be to just add conditions from your second query to the first one, you can use temporary tables to store temporary results. But it would still be better if you put that in a single query.

You could also use subqueries, like SELECT * FROM (SELECT * FROM table WHERE ...) WHERE ....

Upvotes: 0

Pascal MARTIN
Pascal MARTIN

Reputation: 401162

No, there is no such thing as your second idea.


For the first idea, though, I would go with a single query :

select *
from raw_data
where id = $language_id
    and age > 13
    and Salary > 1000

Provided you have set the right indexes on your table, this query should be pretty fast.


Here, considering the where clause of that query, I would at least go with an index on these three columns :

  • id
  • age
  • Salary

This should speed things up quite a bit.


For more informations on indexes, and optimization of queries, take a look at :

Upvotes: 3

Related Questions