|
|
Posted by frizzle on 02/16/06 13:55
Jim Michaels wrote:
> "d" <d@example.com> wrote in message
> news:z4ZHf.19338$wl.17651@text.news.blueyonder.co.uk...
> > "frizzle" <phpfrizzle@gmail.com> wrote in message
> > news:1139609844.558552.125450@f14g2000cwb.googlegroups.com...
> >> 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.
> >
> > Add an index on the date column. :)
>
> 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/web-seminars/coding-indexing.php
>
>
> >
> > dave
> >
> >>
> >> ******************* 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
> >>
> >
> >
'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.
[Back to original message]
|