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
4  2333 
On 10 Feb 2006 14:17:24 -0800, "frizzle" <ph********@gmail.com> wrote: 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 ...
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 :: an**@andyh.co.uk :: http://www.andyh.co.uk http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
"frizzle" <ph********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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. :)
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
"d" <d@example.com> wrote in message
news:z4******************@text.news.blueyonder.co. uk... "frizzle" <ph********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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...g-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
Jim Michaels wrote: "d" <d@example.com> wrote in message
news:z4******************@text.news.blueyonder.co. uk... "frizzle" <ph********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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...g-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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: drife |
last post by:
Hello,
I need to calculate the eigenvectors and eigenvalues for a 3600 X 3600
covariance matrix.
The LinearAlgebra package in Python is incredibly slow to perform the
above calculations...
|
by: DJJ |
last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
tables to a Microsoft Access 2003 database. I am finding that the data from
the MySQL tables takes a hell of a long time...
|
by: mamo74 |
last post by:
Hello.
I am administering a SQL Server (Enterprise Edition on Windows 2003)
from some month and can't understand what is going on in the latest
week (when the db grow a lot).
The DB is around...
|
by: ddsvi78 |
last post by:
I am a complete idiot when it comes to access. Now that said, I work
for a computer security company and one of our customers came to us
with an access problem.
They had been running fine for a...
|
by: roiavidan |
last post by:
Hi,
I'm having a bit of a problem with a small application I wrote in C#,
which uses an Access database (mdb file) for storing financial data.
After looking for a similiar topic and failing to...
|
by: LacrosseB0ss |
last post by:
Hello All;
I have the following code in php. It was working fine up until last week or a bit before then the speed came to a grinding halt. Now when the page tries to load it gets stuck in the...
|
by: jonceramic |
last post by:
Hi all,
I have a situation I can't find any old messages or web-help on.
I have a split database with a bizarre quirk. When I open up the
front-end file, the main form opens up fairly quickly...
|
by: grace |
last post by:
i am wondering why my database retrieval becomes too slow...we set up
a new server (ubuntu, breezy badger) machine where we transferred all
our files from the old server.. Our new server uses Asus...
|
by: existential.philosophy |
last post by:
This is a new problem for me: I have some queries that open very
slowly in design view.
My benchmark query takes about 20 minutes to open in design view. That
same query takes about 20 minutes...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
