MySQL query speed - are multiple tables/table organization necessary?

[ryen]

I haven't used MySQL yet, but plan to start learning soon, so I hope this question isn't that stupid.

Assume I needed a database to hold data for 5 million automobiles, in the US, from all 50 states.. (I just made this up) If I wanted to do a search for a blue van, in Arizona, wouldn't it take forever to perform this SELECT statement if all the data was in one table? EG "SELECT * from cars WHERE type=van color=blue state=AR" ?? I don't know anything about MySQL's search algorythms, but it seems to me that if I had a huge database, I would need multiple tables to be efficient. Such as a table for each state. Or perhaps naming scheme for tables such as "ArizonaVans","ArizonaSUVs",etc.. then I could narrow down the possibilities before even searching.

Any thoughts/advice would be appreciated. Its just something I've been thinking about for a while, but I don't know anyone with MySQL experience, and have yet to read much about it.

Thanks
-Ryen

 

[piti]

hi

when the table is properly indexed, the selects wouldn't last so long
indexes help to speed the selects in tables
for details on indexes look here

http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#MySQL_indexes

 

[ryen]

awesome, i'll definitely check that out, thanks!