How can access to a mysql field in execution time O(1) by select * and use php

Question

assume that we have an mysql table with some rows. We want to check being of a specific records which for instance, its 'index'=0 . By php , after make a query like "SELECT * from My_table" we always use the commands like mysql_fetch_array and then use this structure:

while ( $row = mysql_fetch_array($sql_result) )
{
  if ( $row['index'] == 0 )
       return true;
}

But this method obtain time as O(n) . How can optimize this algorithm to O(1)? and then write whole of the row that we've searched for it?

Note that I want to find the specific row by php not by sql like SELECT ... WHERE index=1. In the other words I want to get the row from the array which yields from mysql_fetch_array.

"we always use the commands like mysql_fetch_array". We are always use mysqli_* or PDO instead of mysql_* functions, since mysql_* functions are deprecated. — vaso123, Nov 06 '14 at 13:37
Like @lolka_bolka said, please read [this question.](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Vanitas, Nov 06 '14 at 13:38
You can use `LIMIT` to do this, e.g. `SELECT * FROM My_table ORDER BY ID LIMIT 120, 1;` will return the 120th row. A **VERY** important note though, is that data in SQL has no implicit order, in order for "Row 20" to have any meaning at all, you **must** supply an `ORDER BY` clause. — GarethD, Nov 06 '14 at 13:39
This also sounds like an [XY Problem](http://meta.stackexchange.com/q/66377/179361), I am struggling to think of a scenario where obtaining an array, iterating through it to find a value, then querying that row would be the best solution. Could be worth giving more detail on what you are trying to achieve, someone may be able to suggest a better way of achieving it. — GarethD, Nov 06 '14 at 13:43
The only `O(1)` **possible** solution is having MySQL return the specific record. Everything else, by definition, will not and can not be `O(1)`. — N.B., Nov 06 '14 at 14:03

MatsLindh · Answer 1 · 2014-11-06T14:11:35.940

You can't. The best case is O(n), as you'll have to iterate through all the items at one time regardless - either you iterate through them to build a hash table and then query that (which will take at least O(n) in total), or you just return when you find the item.

If you're going to query the structure many times, building the hash will still be at least O(n), but querying will be O(1) (in theory).

But this is an area where actually using the database for what it's good at - retrieving information, and doing it conditionally - is the appropriate solution. The database engine can have indices that will help make the information retrieval close to O(1) (if the database engine builds the hash, or a binary look up structure (O(ln n))).

How can access to a mysql field in execution time O(1) by select * and use php

1 Answers1