Database Design

alsaffar

Hi there,

I want to design a [mySQL] database and Im expecting alot of data to be inserted and selected, so I have some questions in my mind need to be answered 🙂

Q1. If a table has a column called ID of integer data type, and I want to search for that column, does it make a difference of how many other columns in the table?

Q2. If a table has a column called ID of integer data type, and I want to search for that column, does it make a difference if one of the other columns in the table is TEXT data type and has a huge amount of data inside?

Q3. How can I make a fast search for a column? like making the column indexed.

Please Help 😉

Truti

Q1: There is no problem here!

Q2: You can add what erver you want, this don't effect the speed of the searching!

Q3: Make is as simple as porsible... Use SQL, and always remember des more parameters you add in the SQL code, des slower the search will be (a thumb rule!)

mySQL ignorre all int values if you are searching a string!
ID will just be ignorred down here:

If you ask for a ID int value, only the ID column will be selectd, and all others ignorred!

steadyguy

Another thing to think about ... the less you select, the faster the query is. So SELECT id FROM table_name; is a very fast query - much faster than SELECT * FROM table_name; - especially if you have indexed the id column (if it is the PRIMARY KEY, then it will be, automatically). But SELECT id FROM table_name WHERE id=id; will be even faster, because the db will go directly to that row if that field is indexed.

HOWEVER, if the column is not indexed, then MySQL will have to do a full table scan which is quite slow. The point: index as many columns as you think you will need to search for. There's a tradeoff, however: the more indices you have, the slower INSERT queries become, because each index also has to be updated.

paulsbooker

just to reenforce the excellent reply before mine

all fields that are searched on ie
fields that come after a WHERE or
appear in JOINS should be indexed

unless its a field that does not clearly differentiate
ie if its a field that has Y or N in equal measure as the mysql optimizer will probably just go for a full scan in these cases and not use the index

kind regards
paul

alainn

to reinforce both excellent posts before mine :
do not forget how your tables are indexed.
ie :
drop table if exists test;
create table test(
x1 bigint unsigned not null,
x2 bigint unsigned not null,
index both (x1,x2),
index last (x2));

From this a 'where x1=some_value and x2=some_value' will use the 'both' index, as well as a 'where x1=some_value' but 'where x2=some_value and x1=some_value' won't use any index ...