Hi,
I wrote a php+MySQL-based reservations system for a local restaurant.
MySQL is running on AMD Athlon 3000+ 2GHz with 1.5Gb of RAM. The
system is accessed through Intranet by 4-5 machines.
The main reservations table weighs only 40k with around 150 entries,
but I'm getting weird lags in the middle of the pages - half of the
table loads ... LAG! ... then the other half loads. This is especially
annoying considering the table will contain around 3,000 - 5,000
entries per year, entries older than 2 years will be removed.
The lags persist on MySQL server (locally), so it's not a network
issue. The lags are espeically bad when I do a query like "SELECT
name, phone, notes FROM reservations WHERE date > %today%" - it
literally takes 5-6 seconds to pull one row.
I don't use "SELECT ALL" queries, my tables are optimized, etc.
Help!
5  1415 
TristaSD wrote: Hi,
I wrote a php+MySQL-based reservations system for a local restaurant.
MySQL is running on AMD Athlon 3000+ 2GHz with 1.5Gb of RAM. The
system is accessed through Intranet by 4-5 machines.
The main reservations table weighs only 40k with around 150 entries,
but I'm getting weird lags in the middle of the pages - half of the
table loads ... LAG! ... then the other half loads. This is especially
annoying considering the table will contain around 3,000 - 5,000
entries per year, entries older than 2 years will be removed.
The lags persist on MySQL server (locally), so it's not a network
issue. The lags are espeically bad when I do a query like "SELECT
name, phone, notes FROM reservations WHERE date > %today%" - it
literally takes 5-6 seconds to pull one row.
I don't use "SELECT ALL" queries, my tables are optimized, etc.
Help!
You might be better of posting details in a dedicated mysql group. Did
you already try the explain keyword to assure that you have correct
indexes and optimization? http://dev.mysql.com/doc/refman/5.0/en/explain.html
Jonathan The lags persist on MySQL server (locally), so it's not a network
issue. The lags are espeically bad when I do a query like "SELECT
name, phone, notes FROM reservations WHERE date > %today%" - it
literally takes 5-6 seconds to pull one row.
Try adding indexes to fields in the table that you are searching on.
ALTER TABLE reservations ADD INDEX (date);
Let me know how it goes.
Jon Tjemsland
TristaSD wrote: Hi,
I wrote a php+MySQL-based reservations system for a local restaurant.
MySQL is running on AMD Athlon 3000+ 2GHz with 1.5Gb of RAM. The
system is accessed through Intranet by 4-5 machines.
The main reservations table weighs only 40k with around 150 entries,
but I'm getting weird lags in the middle of the pages - half of the
table loads ... LAG! ... then the other half loads. This is especially
annoying considering the table will contain around 3,000 - 5,000
entries per year, entries older than 2 years will be removed.
The lags persist on MySQL server (locally), so it's not a network
issue. The lags are espeically bad when I do a query like "SELECT
name, phone, notes FROM reservations WHERE date > %today%" - it
literally takes 5-6 seconds to pull one row.
I don't use "SELECT ALL" queries, my tables are optimized, etc.
Help!
Sounds like you're running into a serious case of lock contention.
Here's a good article on the topic: http://www.devshed.com/c/a/MySQL/MyS...zation-part-2/
TristaSD wrote:
<snip> The lags persist on MySQL server (locally), so it's not a network
issue. The lags are espeically bad when I do a query like "SELECT
name, phone, notes FROM reservations WHERE date > %today%" - it
literally takes 5-6 seconds to pull one row.
What about when you remove the "%" from condition part (as in
%today%)?
--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/
hahaha I was just implying that some VALID code goes in between % %.
:)
R. Rajesh Jeba Anbiah wrote: TristaSD wrote:
<snip> The lags persist on MySQL server (locally), so it's not a network
issue. The lags are espeically bad when I do a query like "SELECT
name, phone, notes FROM reservations WHERE date > %today%" - it
literally takes 5-6 seconds to pull one row.
What about when you remove the "%" from condition part (as in
%today%)?
--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Jackson Miller |
last post by:
I need to have a web application be able to import large amounts of data
(400,000 rows of 10 columns). I know how to script it and have it running in
the background. However I want to know how I can optimize my insert
statements to try to speed things up.
Will it help if I insert multiple rows at a time? If so, is there a magic
number or range?
The same would also go for updates.
|
by: Apollo |
last post by:
1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0
I have about 20K records that result from the following query. Front end for the
database is ACCESS97 and pulling up 20K records makes a huge performance hit.
For the form in question I am using PASSTHROUGH type query (the one that just
passes everything straight to server without ODBC).
NOTE: souce_for_inquiries_form is the join table and is searchable in the from
(it feeds a...
|
by: gord barq |
last post by:
I have this query which does a left outer join and it takes forever (like
half a day). Here are the results of an explain analysis.
mysql> explain SELECT count(searchresult.title) AS number,
campaigntrack.title, tracknum, trackid FROM campaigntrack LEFT OUTER JOIN
searchresult ON searchresult.title = campaigntrack.title WHERE
campaigntrack.albumid = 1 GROUP BY title ORDER BY tracknum;...
|
by: Joseph Norris |
last post by:
Group,
I have been working with Mysql for about 5 years - mainly in LAMP shops. The
tables have been between 20-100 thousand records size. Now I have a project
where the tables are in the millions of records.
This is very new to me and I am noticing that my queries are really
slowwwwww!
What are the options that I have to speed my queries on the mysql side with
|
by: Soefara |
last post by:
Dear Sirs,
I am experiencing strange results when trying to optimize a LEFT JOIN
on 3 tables using MySQL.
Given 3 tables A, B, C such as the following:
create table A (
uniqueId int not null default 0 auto_increment,
a1 varchar(64) not null default '',
| | |
by: Eric B. |
last post by:
Hi,
I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+ rows that I have to search and do
joins on. Although I have an index set up for it, the joined select will
still take some 400+ seconds to return, which is obviously unacceptable.
This is due to enormous HD access.
Perhaps someone can help me...
|
by: Fabien Penso |
last post by:
Hi.
I need a little help to optimize requests on a table, and I have no
idea how to do it (I optimized already a lot other table, but this one
is a pain). My table looks like :
CREATE TABLE comments (
id int(11) NOT NULL auto_increment,
subject varchar(80) NOT NULL default '',
timestamp timestamp(14) NOT NULL,
|
by: Thanos |
last post by:
Hello all,
can someone advice me some links/infos on how to optimize MySQL server to
execute very complex queries ?
My configuration is as follows : P4 2.8Ghz/512MB running on Fedora Core 1
with latest updates, 2xHDD 36Gb SCSI 10000rpm (RAID 1), MySQL 4.0.22
configured with my-large.cnf, frontend application written in PHP 4.
I need to execute complex queries using multiple joins/unions on large
tables (more than 20,000/30,000 records)...
|
by: Got2Go |
last post by:
Hello Group,
I have a table that has millions of records in it.
About 100 records are added every 5 minutes (one per OIDID) (the sample
provided below has data for 2 OIDIDs (99 and 100)
And I have a webpage that executes 9 queries one after the other, and then
displays the results on the webpage.
When the database was empty, this process was very quick.
But, as the DB grew, it became slower.
|
by: coyote2002 |
last post by:
Hi,
I have big problem with mysql 4.0.18 . Because my database is growing
up, every night records that are older than some date are deleted. But
size of database on disk isn`t smaller after this operation. There are
some solutions to free space by OPTIMIZE TABLE command, but this
command when executes, does temporary copy of database. How can I free
memory and avoid doing temporary copy of database ( because I don`t
enough free space for...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
