Cosmin Ciocan Cosmin Ciocan - 2 months ago 7
MySQL Question

SELECT * FROM table HUNDREDS MILLIONS ROWS

Okay so I have a table which currently has 40000 rows and I need to

SELECT
them all. I use a index for
id
and
url
column and if I have to
SELECT
a value by
id
or
url
it's instant but if I have to
SELECT *
it's very slow. What I'm trying to do is searching my database and output the matches and I did this with a

while($arr = mysqli_fetch_array($query))
{ #code... echo $arr['whatever_i_need']."<br>"; }


$query = mysqli_query($link,"SELECT * FROM table");


In the future I will have hundreds of millions of rows in the database so I would like to return the search results fast in 1 sec or something. If you can give me solutions I would really appreciate! Thanks!

EDIT:

I don't want to display all of the data but I want to loop through it quickly to find all the matches

Answer

If you want speed then you definitely don't want the query to return every row from the table, and then "loop through" every row returned by the query to identify the ones you are interested in returning. That approach might give acceptable performance with small tables, but it definitely doesn't scale.

For performance, you want the database to locate just the rows you want to return, filter out the ones you don't want, and return just the subset.

And that comes down to writing an appropriate SQL query; executing an appropriate SELECT statement.

SELECT t.col1
     , t.col2
     , t.col3
  FROM mytable t
 WHERE t.col3 LIKE '%foo%'
   AND t.col2 >= '2016-03-15'
   AND t.col2 <  '2016-06-15'
 ORDER BY t.col2 DESC, t.col1 DESC
 LIMIT 200

Performance is about making sure appropriate indexes are available and that the query execution is making effective use of the available indexes.