is this query faster or i have to do something to make it fast ?

[Pawan_Agarwal]

Suppose, I have a table that has one million rows in it. That particular table has information related to people of particular country. It has few fields like user_id, name, phone, address, email address and some relevant fields like that. I am keeping user_id as primary key [which is unique]. Table name is people_record. There is no join or stuff like that, I am asking for the simple query.

 

Now, my question is, I have to search single record in a table that has one million rows. So, if I search one record in that table using one user_id, how much time will it take with mysql.

 

The query that I am executing is : select * from people_record where user_id=AAAA_00001

 

Will this query provide the output fast or I have to use some searching and sorting techniques to sort the database. 

 

I am asking the question here to know the better way to query a single row out of one million rows, I have listed the query above and want to know if there is something that i can do here to retrieve the result faster ????

 

Looking forward for the best solution..............Thanks

[Barand]

Because the id is not numeric it needs to be in quotes

select * from people_record where user_id = 'AAAA_00001'

Don't use select * unless you really do need all the columns. Specify what you need. The less you retrieve the faster the query.

 

If the id is not the primary key, create a table index on the id column.

[Pawan_Agarwal]

Thanks for your answer, I am putting the user ID in primary key and i want to know if i want to retrieve all fields of table using user_id and you can see that with one user id , i will get only one record, so, if i am fetching one record at a time from table with one million rows, can you tell me how fast will select query work ??

 

Select * from table_name where user_id='AAAA_00001' 

 

You have listed something about indexing, please elaborate about this ................... 

[Barand]

You have listed something about indexing, please elaborate about this ................... 

 

If you search on a column that isn't indexed then every record has to read to find the ones required. As you are using the primary index then you don't have to worry about adding another (for this query).

 

As for how fast - try it. I haven't got a million people_record table on me right now.

[dalecosp]

You have listed something about indexing, please elaborate about this ...................

They have docs

 

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

[Pawan_Agarwal]

Thanks for your participation here. now i have few questions,

 

>> now i know how to create index in database, so after creating index, is there anything that I have to do OR mysql database will take care of indexing and providing faster output on database queries ??

 

>> Suppose in a table 'X' I have few cols with maximum length as 15,20,25,20,30,10,15,15. The sum of all cols length is 150. My next question is that if I insert one row in table 'X', how much KB will it take to store in database for single row??  Does Indexing creates a difference in creating and managing the memory allocation ??

[dalecosp]

MySQL will take care of the optimization for queries that use indexed columns.

 

If you're not familiar with EXPLAIN in SQL, learn about that.

 

As for space requirements, index files do consume storage space. Here are some links that might help.

 

http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html

http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html