Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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

  • #2
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #4
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    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.
    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....

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    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.
    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

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Quote Originally Posted by helenp View Post
    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.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •