0

i am a newbie when coming to mysql,i have been working with firebase database which is not a relational database,but for mysql i have a performance question on querying columns(filtering) i am doing this car app where there is about 19 identities

ID  type    model   seri    marka   year bodytype   feul    Gear    picurl  km  color   warranty    from-who    Exchangable Conidition  city    neibourhood useride

filter is going to be like this

example: type = 2 and model = 3 and seri = 4 and marka =3 and year = 94 and bodytype = 5 and feul = 2 and gear = 3 and km >= 1000 and warrant = 4 and from-who = 4 and color = 3  and exchangable = 2 and conditition = 2 and city = 3 and neibourhood = 2

which is better FOR Filtering: one table with 19 columns or 2-5 Indexed tables with joins based on ID, because the users will be using the filters before doing the query note(note expecting alot of data in the table or tables at it will be under(10.000 row),becuase each row after 4 weeks of being create will delete it self)

Shadow
  • 33,525
  • 10
  • 51
  • 64
Abd
  • 497
  • 4
  • 14
  • don't use `from` as column name; else you always have to call it `\`from\``. – Martin Zeitler Feb 03 '19 at 06:52
  • i have edited it,you have a point. – Abd Feb 03 '19 at 06:56
  • 2
    10,000 rows is not a whole lot of data. I think you'll find the performance difference between your two proposed approaches to be negligible, unless you have some very demanding users or you expect thousands of hits per minute. My advice is to go the easiest route and refactor if you find the performance isn't what you want it to be. As the saying goes, "premature optimization is the root of all evil." – Sam M Feb 03 '19 at 07:02
  • I would set at least id values for marka, bodytype, color etc and put its readable value in a seperate table to prevent faulty duplicates – Bob Vandevliet Feb 03 '19 at 07:20

1 Answers1

-1

You can use this way to run your query:

SELECT * FROM cars WHERE  type = 2 and model = 3 and seri = 4 and marka = 3 and year = 94 and bodytype = 5 and feul = 2 and gear = 3 and km >= 1000 and warrant = 4 and `from-who` = 4 and color = 3  and exchangable = 2 and conditition = 2 and city = 3 and neibourhood = 2 ;

If you want to make relationship for all the tables you have to follow

  • One to One
  • One to Many
  • Many to Many
Googlian
  • 6,077
  • 3
  • 38
  • 44