PHP - optimize SQL select multiple rows for very LARGE table

Question

I have an extremely large table ~a billion rows.

foreach($idArray as $id){
     'SELECT DIE_X, DIE_Y, LOG_INDEX, TREND_ELEMENT, TREND_DATA FROM trend_data_level_5 WHERE TREND_INDEX =' $id;
     $result = mysql_query($query);
}

This approach takes TOO long as my table grows larger and larger. I'm using an innoDB engine for my SQL database.

I ran a timer and the above loop took ~17 seconds for a table size of ~15 milion. Imagine, how long this would take for a billions rows!

Can I get this time down significantly? If so, what's the best approach?

EDIT: Here's what the table looks like

------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table
                                                                                                                                                |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------+
| trend_data_level_5 | CREATE TABLE trend_data_level_5 (
  LOG_INDEX int(5) DEFAULT NULL,
  DIE_X int(3) DEFAULT NULL,
  DIE_Y int(3) DEFAULT NULL,
  TREND_INDEX int(10) DEFAULT NULL,
  TREND_ELEMENT varchar(3) DEFAULT NULL,
  TREND_DATA int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------+

Is `ID` a key on the table? or at least indexed? How large is `ID` (an int, a varchar, etc.). Assuming it's indexed, do you have enough memory on the machine to load the whole index? — Eric Petroelje, Jan 22 '14 at 19:41
Please edit your post and add the output of `SHOW CREATE TABLE table_1` so we can see its columns, data types, and indexes. — Bill Karwin, Jan 22 '14 at 19:42
Ok, I've made the appropriate edits. Check out the table structure — Mark Kennedy, Jan 22 '14 at 19:46
@FabienWarniez, that's not the bottleneck. $idArray contains just a few elements — Mark Kennedy, Jan 22 '14 at 19:49
With an index on `TREND_INDEX int(10) DEFAULT NULL` this should be fast. — Sergiu Paraschiv, Jan 22 '14 at 19:53
Also, instead of dealing with a table with a billion rows why not split it in X tables for each value of `TREND_INDEX`? Are the values dynamic? — Sergiu Paraschiv, Jan 22 '14 at 19:54
That could be possible. Is splitting into X tables generally a lot faster, if size(X) is small? — Mark Kennedy, Jan 22 '14 at 20:04

score 0 · Answer 1 · answered Jan 22 '14 at 19:54

0

If trend_index is a Primary Key use as so. Otherwise create an index using trend_index: CREATE INDEX B ON T1 (B);

If it is unique make it so CREATE UNIQUE INDEX C ON T1 (C);

For a query that simple that should be enough.

answered Jan 22 '14 at 19:54

RGPT

564
1
7
16

score 0 · Accepted Answer · answered Jan 22 '14 at 19:57

0

First of all I would use the MYSQLI extension. More info here: MySqli manual

Secondly, I don't know how many items could have the $idArray array, but you can do a sentence like this:

$list = implode(",", $idArray);
$query = "SELECT DIE_X, DIE_Y, LOG_INDEX, TREND_ELEMENT, TREND_DATA FROM trend_data_level_5 WHERE TREND_INDEX IN ($list)";
$stmt = $your_mysqli_object->prepare($query);
$stmt->execute();
$stmt->store_result(); // This releases the real transaction and charges the data to PHP

while($stmt->fetch())
{
    //...
}

It might not be the most optimized code (I'm sure that it is not) but, in this case, you only do the query once, and then loop over the result doing your magic.

I hope I've helped you. Let me know if it worked properly or if you found a better solution. I want to learn too!

answered Jan 22 '14 at 19:57

Dani Sancas

1,365
11
27

The `SELECT IN` query seems to run a lot faster than doing individual queries in a loop. I haven't really looked into `mysqli`. Does it offer improved query performance? – Mark Kennedy Jan 22 '14 at 20:49
Well, is a cleaner view of the code, because is object oriented. But I haven't deeply investigated it. I think it should be, because isn't too old and I assume it's properly and optimized built. – Dani Sancas Jan 22 '14 at 20:59
Take a look at this post: http://stackoverflow.com/questions/548986/mysql-vs-mysqli-in-php – Dani Sancas Jan 22 '14 at 20:59

score 0 · Answer 3 · answered Jan 22 '14 at 22:53

0

This is a very basic question. The answer is to use indexes (you have none), and there are plenty of resources on the web to help you figure out how to do that.

answered Jan 22 '14 at 22:53

jeremycole

2,741
12
15

score 0 · Answer 4 · answered Jan 22 '14 at 23:16

I'd suggest using PDO. Then you'll have to use one single prepared query (queries in loops is a really bad practice since it's very slow!), then you'll do a $pdo->fetchAll(), which will bring you a bidimensional array (or array of arrays, if you want). Then you can safely do your foreach through that array.
Let me know if you need further explanations.

PHP - optimize SQL select multiple rows for very LARGE table

4 Answers4