Mysql Variables - Little Question

gtk · #1 (**permalink**) 05-10-07, 08:41

Hi,

I have to do several queries that stores data in some temporary tables,
For almost each, I need to filter using a certain preselection,
let me give you an example:

Code:

DROP TEMPORARY TABLE IF EXISTS tmp_data1;

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_data
SELECT
 src.id AS "id"
 , src.name AS "name"
 , 1 AS "source"
FROM
 table_source1 AS src
WHERE
 src.filter IN (1, 3, 5, 16, 21)
;

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_data
SELECT
 src.id AS "id"
 , src.name AS "name"
 , 2 AS "source"
FROM
 table_source2 AS src
WHERE
 src.filter IN (1, 3, 5, 16, 21)
;

SELECT * FROM tmp_data;

Is there a way to use a MySQL variable to store the filters ?
By doing something like that:

Code:

@filters = array{1, 3, 5, 16, 21};
...
WHERE
 src.filter IN @filters
...

Otherwise I was thinking to use another temporary table with
the filters in it but I'm not sure that is the good solution:

Code:

DROP TEMPORARY TABLE IF EXISTS tmp_filters
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_filters
 (filter INT, PRIMARY KEY (filter))
SELECT 21 AS "filter";

INSERT INTO tmp_filters (filter) VALUES((1), (3), (5), (16), (21));

...
FROM
 table_source2 AS src
 JOIN tmp_filters AS flt ON (src.filter = ftl.filter)
;
...

guelphdad · #2 (**permalink**) 05-10-07, 10:52

I don't think you can do that with a variable. Why do you need to store it and not do it at query time?

By the way why are you creating temp tables and not doing a simple union?

Code:

SELECT * FROM
(
SELECT
 src.id AS "id"
 , src.name AS "name"
 , 1 AS "source"
FROM
 table_source1 AS src
WHERE
 src.filter IN (1, 3, 5, 16, 21)

UNION 

SELECT
 src.id AS "id"
 , src.name AS "name"
 , 2 AS "source"
FROM
 table_source2 AS src
WHERE
 src.filter IN (1, 3, 5, 16, 21)
) AS derived_table

r937 · #3 (**permalink**) 05-10-07, 13:41

the whole idea of using a temporary table needs to be looked at

i don't think you need one

gtk · #4 (**permalink**) 05-11-07, 04:50

This was just an example.
The question is not to know if I need to create temporary tables
for storing my data, but to find a mean to use a "common filter".
The aim is not to have to put the filter each time with
src.filter IN (1, 3, 5, 16, 21) for maintenance reason.
Actually I could merge the queries in one
but I can't tell MySQL how to optimize the JOINs here
So the splitted version is 100x quicker.

Let me enclose you a file with the true queries for info.

guelphdad · #5 (**permalink**) 05-11-07, 08:38

Why do you have separate tables in the first place rather than a column in a single table denoting which of your current tables the data belongs in? I don't know your data exactly, but it seems to me it should be in a single table.

As for the variables, like I said, within mysql I don't think you can do that because you can't store variables like an array. If you were using PHP or PERL or whatever, then you could use that, explode it and search on it.

r937 · #6 (**permalink**) 05-11-07, 09:17

Quote:

Originally Posted by gtk

This was just an example.

in that case, my reply was just an example too

but you still don't need a temporary table

HereSomeHow · #7 (**permalink**) 05-11-07, 11:12

Storing the values in another table would do. With a table called filters with a single column filtervalue, and using something like this:

Code:

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_data
SELECT
 src.id AS "id"
 , src.name AS "name"
 , 2 AS "source"
FROM
 table_source2 AS src
WHERE
 src.filter IN (SELECT filtervalue FROM filters)
;

I tested it in my db and worked...

Internet Services

Web Development

Digital Marketing

Consumer Tech