Question 186149 in Mysql Question posted in
The official MySQL documentation can be found here.

Optimize mysql query with bit(12) field

I have a huge mysql table where each record is a trip.
Now I have to add information to each trip to say in which months the trip will start.

The first idea was to use a bit(12) field where each bit represents a month.

But when I query the table to find the trips for certain months, the queries are very slow because the whole table must be analyzed by doing a "bitwise and" (&) between the bit(12) field and the mask of the months.

select * when (months & b’100100000000′) > 0

Is it a better solution to use 12 boolean fields (one per month) putting in the where condition some or?

select * from table when (jan=1) or (apr=1)

And can a field index on single months help in this case?

Are there any better solutions to optimize query execution time when I want to find all the trips that depart in certain months?

Tags:

2

Answers


  1. 0

    I would definitely used 12 boolean fields. Not only speed but also further maintenance of code will get much better. You can play with indexing to see if it speeds your queries up. I suppose indexing months will not change the performance considerably. Also remember to backup your table data before you change structure of your table.

    Login or Signup to reply.
  2. 0

    Answering your question from the comments.

    If you just want to keep the starting month and do not care about the year, you can use table:

    create table trip_starts (
trip_id int,
month_number int,
primary key (month_number, trip_id)
)

    To query the trips that start in January or April you can do:

    select t.* 
from trips t
  join trip_starts ts on ts.trip_id=t.id
where ts.month_number in (1,4)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search