CodingForums.com - index and how many years in calendar table

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - index and how many years in calendar table (http://www.codingforums.com/showthread.php?t=248508)

index and how many years in calendar table

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:

Code:

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 )")

Code:

SELECT property, description, COUNT(description) as type

FROM calendar_table WHERE property = '$propiedad'

AND cal_date BETWEEN ('$llegada') AND ('$newdate') GROUP BY description

Code:

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:

Code:

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

Code:

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

Added,
This is copied from phpmyadin:

Code:

Indexes: Documentation

Action        Keyname        Type        Unique        Packed        Column        Cardinality        Collation Null        Comment



Edit Edit         Drop Drop         PRIMARY        BTREE        Yes        No        id        25585        A                

Edit Edit         Drop Drop         cal_date        BTREE        No        No        cal_date        731        A                

                                                                        property        25585        A

What I dont understand is that property appears but I cant edit etc.
Is that a suggestion that I would do it an index? or that I had it as index but deleted it?

if you have an indexed table you should not notice slowdown in your queries until you are in the tens or hundreds of millions of rows in the table providing the query is able to make use of the index.

if you have an index on people but have a where clause on address for example, the index wouldn't be used.

Quote:

Originally Posted by guelphdad (Post 1179780)

thanks, so I should definivetely add more years, but I still arent sure if this is true or not:
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.
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.

If this is not true, then I should also do a index of column property....

Just FYI.

I have an *ACCESS* database (which is typically 10 times slower than MySQL) which has *FOUR* records for EVERY date from 2006 to 2019.

And you can't even notice any performance hit.

THINK about it: If you have one record for every date for (say) 10 years, you will *STILL* have LESS than 4000 records! Even if every record was 1000 bytes long, you'd have less than 4 megabytes of data. When your MySQL Server computer has 4GB or so of memory, 4MB is *NOISE*. It will be loaded completely into memory and all queries will, effectively, be done at memory speed.

Yes, when I first started working with databases on computers that had as little as 32 KILObyte of memory, it was really important to keep data size to a minimum. But you have to be realistic about how powerful modern computers are.

About a year ago, I was working on a MySQL database that GREW by 1 GIGABYTE every 20 minutes. 3 GIGABYTES PER HOUR. *AND* we were making inquiries against that data at the same time it was growing at that rate. Fairly complex SQL queries, including one stored procedure that I remember that was about 7 printed pages of SQL code.

MySQL may be a free database, but that doesn't mean it's a toy. If and when you find your queries beginning to slow down, *then* you can worry about what to do to improve them.

BUt having a calendar table that is ten years long? That's a baby database.

And yes, you can and should index columns where data is repeated. *IF* those columns are in the critical path of the query execution.

MySQL has an EXPLAIN statement that will tell you when a query is not as efficient as it should be. Read about it and learn how to use it.

Quote:

Originally Posted by Old Pedant (Post 1179851)

Thanks for the explanation,
I have been reading trying understand and I read that mysql will only use one index per table, so when I have a where clause with 2 conditions there is no need to index the property table, just the date.

I was worried as it will be about 50 rows per date, and as it is a shared hosting I suppose the speed is not the same as a dedicated host.
I will add 10 or 20 years and test, I can always delete some years if there are 2 many.
Thanks a lot

Quote:

Originally Posted by helenp (Post 1179872)

I read that mysql will only use one index per table,...

Not true. *PER* join, probably true. Maybe even per condition in the WHERE clause. But if you have multiple WHERE conditions, it can take advantage of multiple indexes. (It may not choose to. But that's a different thing. If a single index reduces the number of candidate records down to just a few, it's faster to then scan the records then invoke another index.)