[helenp]

Hi, It looks I finally have more or less everyting necesarry to finish my calendar table wich I will use to calculate prices etc.
In the calendar table I will have a max of 50 properties, and one row for each date and property in a datecolumn called cal_date.
The questions are, how many years can I have without loosing speed in a shared hosting?
My initial idea was to have only 2 years and when 1 year finish update the year that passed to next year, ie. 1/1 2014 I would change 2012 to 2014, so then I will have 2013 and 2014 in the table.
However that means that every 4 years I will have to drop the 29 february, wich is not complicated, but I dont like to add the 29 february as that would have to be done property by property to insert all the information.
So maybe I can have more years if its indexed correctly.

This is a show create table of the table in question:
calendar_table CREATE TABLE `calendar_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`cal_date` date NOT NULL,
`property` varchar(25) NOT NULL,
`price_client` decimal(12,2) NOT NULL,
`description` varchar(20) NOT NULL,
`price_owner` decimal(12,2) NOT NULL,
`elena` decimal(5,2) NOT NULL,
`elena_n` decimal(7,2) NOT NULL,
`minimo` varchar(20) NOT NULL DEFAULT '0',
`price_client_week` decimal(12,2) NOT NULL,
`price_owner_week` decimal(12,2) NOT NULL,
`startdate` date DEFAULT NULL,
`enddate` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `cal_date` (`cal_date`,`property`)
) ENGINE=MyISAM AUTO_INCREMENT=25586 DEFAULT CHARSET=latin1

The id column I added afterwards when I was trying to do queries, and at this moment I dont need it, so dont know if I should drop it.
Column cal_date I have as index, and that column have repeated values, ie,
if I have 50 properties, then the same date is repeated 50 times.
Column property I dont know if I should do index or not as I use it in where clauses same as column cal_date, however the value in this column is repeated 352 times per year, one per each daterow.

The queries I will have for users to use on website are not complicated,
these are the queries:

SELECT property, TO_DAYS( '$salida' ) - TO_DAYS( '$llegada' ) AS dias, COUNT(*)  AS theCount
FROM calendar_table
WHERE property = '$propiedad'
AND DATE( cal_date )
BETWEEN '$llegada' AND '$salida'
GROUP BY property
HAVING theCount = ( 1 + dias )")

SELECT property, description, COUNT(description) as type
FROM calendar_table WHERE property = '$propiedad'
AND cal_date BETWEEN ('$llegada') AND ('$newdate') GROUP BY description

SELECT SUM(price_client) as price, minimo, property FROM calendar_table
WHERE property = '$propiedad'
AND cal_date BETWEEN ('$llegada') AND ('$newdate')"

I also have queries for internal use, that are to check availability, prices and get more information, these are more complicated and use joins, however its for internal use:

SELECT buscador.id_propiedad, calendar_table.property, minimo,
llegada, salida, propiedad, link_ingles, link_esp, link_sve, duermen FROM buscador 
LEFT JOIN calendar_table on buscador.id_propiedad = calendar_table.property 
LEFT JOIN bookings ON propiedad = buscador.id_propiedad AND (llegada <= '$llegada'
AND salida >= '$llegada' + INTERVAL 1
DAY
OR salida <= '$salida'
AND llegada >= '$salida' + INTERVAL -1
DAY or 
llegada <= '$llegada' AND salida >= '$salida' or llegada >= '$llegada' 
AND salida <= '$salida') WHERE 
propiedad is NULL and cal_date BETWEEN ('$llegada') AND ('$newdate') 
group by buscador.id_propiedad

SELECT SUM(price_owner) as price, minimo, casa.id_propiedad, property FROM calendar_table
Left Join casa ON calendar_table.property = casa.id_propiedad WHERE cal_date BETWEEN 
('$llegada') AND ('$newdate') AND NOT EXISTS
(SELECT llegada, salida, propiedad from bookings 
WHERE bookings.propiedad = calendar_table.property AND
(('$llegada' BETWEEN llegada AND date_sub(salida, interval +1 day)) 
or ('$salida' BETWEEN date_sub(llegada, interval -1 day) AND salida) or (llegada <= '$llegada' AND salida >= '$salida') 
or (llegada >= '$llegada' AND salida <= '$salida'))) 
GROUP BY calendar_table.property

Any suggestions?
Should I add 10 or 20 years instead of only 2?
And which columns should be indexes?
As I understood you need index when you use where clause for the column and there are many rows, however not sure its true but I also read somewhere that if the values of the column is repeated many times you should not index the columns.
Thanks in advance,
Helena