Php, Mysql - How to working with big data in my case

Question

I have two table with big data, Example in http://www.sqlfiddle.com/#!2/ee61b/3

Table 1 like

CREATE TABLE `t1`(
  `id` int(4) ,
  `name` varchar(40),
  `signal` int(4)
)
;

INSERT INTO `t1`
(`id`, `name`, `signal`)
VALUES
    (1, 'a', 1),
    (2, 'b', 1),
    (3, 'c', 0),
    (4, 'd', 0)
;

And table 2 like

CREATE TABLE `t2`(
  `uid` int(4),
  `type` int(4),
  `usignal` int(4)
)
;

INSERT INTO `t2`
(`uid`, `type`, `usignal`)
VALUES
    (3, 1, 1),
    (1, 2, 1),
    (4, 1, 0),
    (2, 2, 0)
;

Now i try to get all record have conditional like
1. All record in table 1 has signal = 1
2. OR(+) all record in table 1 has id in (SELECT uid FROM t2 WHERE t2.type = 1 AND t2.usignal = 1)

I do that like

SELECT * FROM `t1` AS g1
WHERE g1.signal = 1
UNION 
SELECT * FROM  `t1` AS g1
WHERE g1.id
IN (
    SELECT uid
    FROM  `t2` AS g2
    WHERE g2.usignal = 1 AND g2.type = 1
)

But I must do some conditional on that (mybe join,...) and i do that like

SELECT * FROM `t1` AS g1
WHERE g1.signal = 1
UNION 
SELECT * FROM  `t1` AS g1
WHERE g1.id
IN (
    SELECT uid
    FROM  `t2` AS g2
    WHERE g2.usignal = 1 AND g2.type = 1
)

/*dynamic other join query here */
and id>=1 /*dynamic conditional query here*/
order by id desc limit 0,20

That working well if data is small, But my data is bigger and that fail while execute

How can i do that thanks

Edit:
What about my sql? Is that make execute slowly and make it timeout?

Edit02:
I using phpmyadmin and click index to t1(id, signal) and t2(type, unsignal)
And i add id primary key fo both table example in http://www.sqlfiddle.com/#!2/d88a9/1
But in real my tables have about 10 column. And i using left join but that's still timeout :(

Answer 1

Index t1 on signal, and t2 on (usignal,utype).

Define your primary keys as well.

Answer 2

Do you have any indices? Make sure that you created the proper ones.

For t1 it seems that you need an index on (id, signal) tuple. For t2 you need an index on (usignal, type) tuple.

This is how you can add them if you already don't have (according to the create tables you don't):

create index 'id_signal_index' on t1 (`id`, `signal`);
create index 'id_usignal_index' on t2 (`usignal`, `type`);

It is usually a good practice to have primary key for id with auto increment in a table like t1.

Also convert the inner query into left join query, which is explained in other answers.

Answer 3

In your SQL-Fiddle http://www.sqlfiddle.com/#!2/ee61b/3 there is an option down below in the green navigation bar: "+ view execution plan". You can also get to this information by adding "EXPLAIN" to the front of your query.

In you case the result looks something like this:

+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra       |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
|    1 | PRIMARY      | g1          | ALL    | NULL          | NULL         | NULL    | NULL |    4 | Using where |
|    2 | UNION        | g1          | ALL    | NULL          | NULL         | NULL    | NULL |    4 |             |
|    2 | UNION        | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 |             |
|    3 | MATERIALIZED | g2          | ALL    | NULL          | NULL         | NULL    | NULL |    4 | Using where |
| NULL | UNION RESULT | <union1,2>  | ALL    | NULL          | NULL         | NULL    | NULL | NULL |             |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+

Look at the column "possible_keys": you have NULL everywhere. That is bad, it means that the database has to search sequentially through your data to find something.

If you add the right primary keys and indexes to your database, then every search will be done using a clever data structure like a tree, and will be much faster, especially for large date:

+------+--------------+------------+--------+-----------------+---------+---------+-------------+------+-------------+
| id   | select_type  | table      | type   | possible_keys   | key     | key_len | ref         | rows | Extra       |
+------+--------------+------------+--------+-----------------+---------+---------+-------------+------+-------------+
|    1 | PRIMARY      | g1         | ref    | signal          | signal  | 5       | const       |    2 |             |
|    2 | UNION        | g2         | ref    | PRIMARY,usignal | usignal | 5       | const       |    2 | Using where |
|    2 | UNION        | g1         | eq_ref | PRIMARY         | PRIMARY | 4       | test.g2.uid |    1 |             |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL            | NULL    | NULL    | NULL        | NULL |             |
+------+--------------+------------+--------+-----------------+---------+---------+-------------+------+-------------+

So: read up on Primary Keys and Indexes and apply this knowledge to your example.

Answer 4

Try to get rid of your sub query and use join , i have also removed the union from the query and for this i have enclosed the conditions in braces to meet your union needs

SELECT g1.* FROM `t1` AS g1
left join `t2` AS g2 on (g1.id=g2.uid)
WHERE (
(g2.usignal = 1 AND g2.type = 1 )
 OR g1.signal = 1      
  )
order by id desc limit 0,20

Also make sure you have proper index on your columns

See this fiddle

Answer 5

Have you tried joining instead of that nested select (which won't be fast)?

SELECT g1.* FROM `t1` AS g1
LEFT JOIN `t2` AS g2 ON g2.uid = g1.id 
WHERE g1.signal = 1
 OR (g2.usignal = 1 AND g2.type = 1)

/*dynamic other join query here */

and id>=1 /*dynamic conditional query here*/
order by id desc limit 0,20

Answer 6

What error do you get so that we can answer your question .. you can set the maximum execution time from php ini like this :ini_set('max_execution_time', 300); And your task will work for 300 seconds instead of 30 .