Incredibly slow database

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • frizzle
    #1

    Incredibly slow database

    Hi group.

    I have a news management system, with a mySQL backend.
    I tested it yesterday with 1.000.000+ records, testing my url system.
    I pulled out records calling them by the url field. It was incredibly
    fast, but now,
    when i call 5 records, ordered by date (which were inserted randomly)
    it's incredibly slow. And i only have 100.000 records left at the
    moment.
    My database structure is below, and i son't know how i could get it to
    run
    any faster ...

    Frizzle.


    *************** **** DATABASE *************** *****


    Field -> Type -> Null -> Standard Value

    id int(11) No auto_increment
    author_id int(11) No
    author_ip int(11) No 0
    created datetime No 0000-00-00 00:00:00

    published datetime No 0000-00-00 00:00:00

    title varchar(35) No
    url varchar(35) No
    text text Yes NULL
    visible tinyint(1) No 1
    sticky tinyint(1) No 0
    commentable tinyint(1) No 1


    Keyname Type Amount Field
    PRIMARY PRIMARY 100000 id
    id UNIQUE 0 id
    url UNIQUE 1 url
    published INDEX 100000 published

    Tags: None
    • Andy Hassall
      #2
      Re: Incredibly slow database

      On 10 Feb 2006 14:17:24 -0800, "frizzle" <phpfrizzle@gma il.com> wrote:
      [color=blue]
      >I have a news management system, with a mySQL backend.
      >I tested it yesterday with 1.000.000+ records, testing my url system.
      >I pulled out records calling them by the url field. It was incredibly
      >fast, but now,
      >when i call 5 records, ordered by date (which were inserted randomly)
      >it's incredibly slow. And i only have 100.000 records left at the
      >moment.
      >My database structure is below, and i son't know how i could get it to
      >run
      >any faster ...[/color]

      You should learn to use the EXPLAIN command, which shows how MySQL is
      executing the query. You can use this information to make changes to your
      schema, the simplest of which is adding extra indexes to support your queries.

      Follow-ups set to comp.databases. mysql.

      --
      Andy Hassall :: andy@andyh.co.u k :: http://www.andyh.co.uk
      http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

        Comment

        • d
          #3
          Re: Incredibly slow database

          "frizzle" <phpfrizzle@gma il.com> wrote in message
          news:1139609844 .558552.125450@ f14g2000cwb.goo glegroups.com.. .[color=blue]
          > Hi group.
          >
          > I have a news management system, with a mySQL backend.
          > I tested it yesterday with 1.000.000+ records, testing my url system.
          > I pulled out records calling them by the url field. It was incredibly
          > fast, but now,
          > when i call 5 records, ordered by date (which were inserted randomly)
          > it's incredibly slow. And i only have 100.000 records left at the
          > moment.
          > My database structure is below, and i son't know how i could get it to
          > run
          > any faster ...
          >
          > Frizzle.[/color]

          Add an index on the date column. :)

          dave
          [color=blue]
          >
          > *************** **** DATABASE *************** *****
          >
          >
          > Field -> Type -> Null -> Standard Value
          >
          > id int(11) No auto_increment
          > author_id int(11) No
          > author_ip int(11) No 0
          > created datetime No 0000-00-00 00:00:00
          >
          > published datetime No 0000-00-00 00:00:00
          >
          > title varchar(35) No
          > url varchar(35) No
          > text text Yes NULL
          > visible tinyint(1) No 1
          > sticky tinyint(1) No 0
          > commentable tinyint(1) No 1
          >
          >
          > Keyname Type Amount Field
          > PRIMARY PRIMARY 100000 id
          > id UNIQUE 0 id
          > url UNIQUE 1 url
          > published INDEX 100000 published
          >[/color]


            Comment

            • Jim Michaels
              #4
              Re: Incredibly slow database


              "d" <d@example.co m> wrote in message
              news:z4ZHf.1933 8$wl.17651@text .news.blueyonde r.co.uk...[color=blue]
              > "frizzle" <phpfrizzle@gma il.com> wrote in message
              > news:1139609844 .558552.125450@ f14g2000cwb.goo glegroups.com.. .[color=green]
              >> Hi group.
              >>
              >> I have a news management system, with a mySQL backend.
              >> I tested it yesterday with 1.000.000+ records, testing my url system.
              >> I pulled out records calling them by the url field. It was incredibly
              >> fast, but now,
              >> when i call 5 records, ordered by date (which were inserted randomly)
              >> it's incredibly slow. And i only have 100.000 records left at the
              >> moment.
              >> My database structure is below, and i son't know how i could get it to
              >> run
              >> any faster ...
              >>
              >> Frizzle.[/color]
              >
              > Add an index on the date column. :)[/color]

              he *has* an INDEX on the date column. see "published" down below. "created"
              doesn't though. I don't know if maybe that is the issue he's talking about.
              If there's an index already on that column, it's out of my league. However,
              on March 1, MySQL is hosting a webinar on the subject "Coding and Indexing
              Strategies for Optimal Performance"
              http://www.mysql.com/news-and-events/web-seminars/
              To register go to:
              http://www.mysql.com/news-and-events...g-indexing.php

              [color=blue]
              >
              > dave
              >[color=green]
              >>
              >> *************** **** DATABASE *************** *****
              >>
              >>
              >> Field -> Type -> Null -> Standard Value
              >>
              >> id int(11) No auto_increment
              >> author_id int(11) No
              >> author_ip int(11) No 0
              >> created datetime No 0000-00-00 00:00:00
              >>
              >> published datetime No 0000-00-00 00:00:00
              >>
              >> title varchar(35) No
              >> url varchar(35) No
              >> text text Yes NULL
              >> visible tinyint(1) No 1
              >> sticky tinyint(1) No 0
              >> commentable tinyint(1) No 1
              >>
              >>
              >> Keyname Type Amount Field
              >> PRIMARY PRIMARY 100000 id
              >> id UNIQUE 0 id
              >> url UNIQUE 1 url
              >> published INDEX 100000 published
              >>[/color]
              >
              >[/color]


                Comment

                • frizzle
                  #5
                  Re: Incredibly slow database


                  Jim Michaels wrote:[color=blue]
                  > "d" <d@example.co m> wrote in message
                  > news:z4ZHf.1933 8$wl.17651@text .news.blueyonde r.co.uk...[color=green]
                  > > "frizzle" <phpfrizzle@gma il.com> wrote in message
                  > > news:1139609844 .558552.125450@ f14g2000cwb.goo glegroups.com.. .[color=darkred]
                  > >> Hi group.
                  > >>
                  > >> I have a news management system, with a mySQL backend.
                  > >> I tested it yesterday with 1.000.000+ records, testing my url system.
                  > >> I pulled out records calling them by the url field. It was incredibly
                  > >> fast, but now,
                  > >> when i call 5 records, ordered by date (which were inserted randomly)
                  > >> it's incredibly slow. And i only have 100.000 records left at the
                  > >> moment.
                  > >> My database structure is below, and i son't know how i could get it to
                  > >> run
                  > >> any faster ...
                  > >>
                  > >> Frizzle.[/color]
                  > >
                  > > Add an index on the date column. :)[/color]
                  >
                  > he *has* an INDEX on the date column. see "published" down below. "created"
                  > doesn't though. I don't know if maybe that is the issue he's talking about.
                  > If there's an index already on that column, it's out of my league. However,
                  > on March 1, MySQL is hosting a webinar on the subject "Coding and Indexing
                  > Strategies for Optimal Performance"
                  > http://www.mysql.com/news-and-events/web-seminars/
                  > To register go to:
                  > http://www.mysql.com/news-and-events...g-indexing.php
                  >
                  >[color=green]
                  > >
                  > > dave
                  > >[color=darkred]
                  > >>
                  > >> *************** **** DATABASE *************** *****
                  > >>
                  > >>
                  > >> Field -> Type -> Null -> Standard Value
                  > >>
                  > >> id int(11) No auto_increment
                  > >> author_id int(11) No
                  > >> author_ip int(11) No 0
                  > >> created datetime No 0000-00-00 00:00:00
                  > >>
                  > >> published datetime No 0000-00-00 00:00:00
                  > >>
                  > >> title varchar(35) No
                  > >> url varchar(35) No
                  > >> text text Yes NULL
                  > >> visible tinyint(1) No 1
                  > >> sticky tinyint(1) No 0
                  > >> commentable tinyint(1) No 1
                  > >>
                  > >>
                  > >> Keyname Type Amount Field
                  > >> PRIMARY PRIMARY 100000 id
                  > >> id UNIQUE 0 id
                  > >> url UNIQUE 1 url
                  > >> published INDEX 100000 published
                  > >>[/color]
                  > >
                  > >[/color][/color]

                  'created' doesn't have an index to it, but no queries sort things
                  according to creation date. Admins can cretae things today, and set
                  them to be published e.g. tomorrow 12:00

                  Frizzle.

                    Comment

                    Previous template Next
                    Working...