Php - Sorting By High / Low Price With Data?

Oct 26, 2012

I have 12 items in my MySql database. 4 products are 4.99, 4 products are 3.99 and 4 products are 2.99.

I realize I can query the database like this, and It will give me a list of products at that price:

<?php
$query = "SELECT * FROM UFPProducts WHERE price = 4.99";
$results = mysql_query ($query, $connect);
while ($row = @ mysql_fetch_array($results))
{

[code].....

However, what I want, is a button I can press to sort products by price.

ADVERTISEMENT

Add An Option Of Sorting Items By Price Range

Apr 11, 2011

I'm looking to add an option of sorting items by price range and have found the following query example from another source but it produces syntax errors.
[Code]....

View 5 Replies View Related

Select Products Based On Price Where Discount_price Trumps Actual Price

Jun 7, 2010

I have a table of prices that looks something like this:

id    price    discount_price
1    10.00    9.50
2    12.50    0.00
3    15.00    12.00

I need a way to be able to select products based on price where the discount_price trumps the actual price as long as the discount price is greater than 0.00. I need to do this in the sql.

I had thought about using least(price, discount_price), but can't see a way to ignore discount_price when it is zero.

View 2 Replies View Related

JavaScript - Price Update Working But Not With Multiple Select And Discounting Price At The End

May 16, 2012

I have multiple dropdown lists calculating a total price at the end, it works great apart from a few issues.

Firstly, I would like to add further prices to it, so it should read:

Total : £20.97

V.A.T : £4.19

Total Amount : £25.16

I'm sure this is simple but i cannot think how?

Secondly - It only works on a single selection drop down lists, if i want to select multiple options in one list, it will only calculate one

Here's the code i use to calculate the price:

$(function(){
$(".calculate").on("change", function(){
var total = 0;

[Code]....

Thirdly, there are about 8 dropdown lists but i want the last one to discount the total price instead of adding to it, the code above takes the price from option value of each selection and so will the discount amount (In the discount column in the database, it's written as 10%.

So basically, after 7 lists i should have a total of i.e. £20.97 then once the last list is selected, it should replace the price with a discount amount i.e. (-10%) £18.87

View 3 Replies View Related

Detecting Price Changes In Price History Table

Oct 7, 2010

I have an application where I capture (to a MYSql 5.1.50 Community database) retailer price history once per day, across 10 retailers, for each of 2000 SKUs. I need to generate a report, segregated by retailer, where there is a variance of the part’s Price value in the past N days. Table depth is limited to 600,000 records, as any data > 30 days old is culled to an archive.

Price data is presently contained in a table with this structure:

PK- table primary keyVendorFK (Int) – foreign key to the vendor table MySKUID (Int) – foreign key to the master products listVe ndorSKUID (VarChar45) – the retrieved vendor SKU for the given Product in question (vendor’s internal SKU)DateCheckedPrice

The field VendorSKUID is unique for a given VendorFK. The client application can do a lot of the work (if required to simplify the process); I could break this up into 10 queries, one per retailer, and then integrate the results sets on the client side. Ideally, the query will only return the VendorFK and the VendorSKUID for the SKS(s) with price change history, and then the client will store this in memory for a second series of queries; that way, I do not have to worry about variable record counts in the case a particular vendor did not report pricing for one or more days during the period in question.

SQL is not my forte, and most of my experience is with SQLServer, not MySQL, so Im a bit lost with this. I have tried a number of approaches, with no progress.

View 2 Replies View Related

Setup For High Score - Print Returned Data

Apr 28, 2013

Mysql version 5.0Currently i have it set up for high scores.I can correctly send NAME and SCORE to the mysql table. It is set up to automatically assign an "id" to the input using auto_increment.How can i ask the server for the "id" after i input the name and score and then print it on the page?

View 1 Replies View Related

Php - Pass JSON Data To High Charts Without Success?

Nov 25, 2012

I am trying to parse json data created by a php file to another script and display its values to a highchart. My data.php which creates the json data is this:

<?php
header("Content-type: application/json");
$dbhost = "localhost";
$dbuser = "db";
$dbpass = "xxxxx";

[code]...

the data.php output is:

[{"date":"2012-11-23","count":"582311"},{"date":"2012-11-24","count":"582322"},{"date":"2012-11-22","count":"582121"},{"date":"2012-11-21","count":"581321"},{"date":"2012-11-19","count":"572821"},{"date":"2012-11-20","count":"581321"},{"date":"2012-11-18","count":"582421"},{"date":"2012-11-17","count":"579321"},{"date":"2012-11-16","count":"581321"},{"date":"2012-11-25","count":"558178"}]
inside <script>:

[code]...

How i create a serie named total players with 'item.date' in x-axis and 'item.count' in y-axis???

UPDATE:
i add this inside each:
monitor_graph.xAxis[0].setCategories([item.date]);
monitor_graph.series[0].setData([parseFloat(item.count)]);

and i now get the 1 point exactly as i need it but with the following error:

Unexpected value NaN parsing y attribute.

View 2 Replies View Related

Plotting Multiple Series Of Data On High-stock?

Jul 18, 2013

Have a personal temperature monitor project that logs all temperatures in separate rooms at home into a mysql database and I came across highstock/charts the other day and have been playing with it, but I cant seem to get it to work with multiple series of data.how the data is logged into the table;

datetime <-- (eg; 2013-07-18 15:52:26) time will be different for each sensor for each location
location <-- 6 of (lounge,kitchen,dinning,outside,master,spare)
temperature <-- (eg. 12.34)

What a record looks like in mysql;
2013-07-18 15:52:26 / master / 12.34

I have managed to get it all working for set of data, but I'm not sure what I need to do and how the best way is to format the json and mysql query so that highcharts can read the json/mysq data and plot ALL 6 locations temperature data on the one graph.Complete code; The main highstock chart file. [URL] and this is the file that generates the JSON from the mysql database. [URL]

This is what it currently looks like for one set of data using the above queries.... [1374593356000,17.31],[1374593427000,17.25],[1374593497000,17.31],[1374593567000,17.31],[1374593638000,17.31],[1374593708000,17.25],[1374593778000,17.25],[1374593849000,17.25],[1374593919000,17.25],[1374593989000,17.25],[1374594060000,17.25],[1374594130000,17.25]....etc

So my questions are;What is the best way to change this so it plots all the data, at the moment it doesn't seem to work;
mysql_select_db("mqtt", $con);
$return_arr = array();
$fetch = mysql_query("SELECT timeof, message FROM temperatures WHERE DATE(timeof) BETWEEN

[code]...

I assume for multiple sets of data that it needs to be in this format.
[{name:'kitchen',dat‌​a:[[date,temp],[date,temp],[date,temp]]},{name:'lounge',dat‌​a:[[date,temp],[date,temp],[date,temp]]}]

Not sure how to change my query so that it pulls the data out for ALL locations and then encodes it correctly.

UPDATE-1
Latest code,
$return_arr = array();
$fetch = mysql_query("SELECT timeof, locationmap AS location, message AS temp FROM temperatures WHERE DATE(timeof) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE() ORDER BY location, timeof

[code]...

However it doesn't return the correct format as mentioned above.It comes out like this. (Obviously there is 1000's of entires.)

[1375272426000,22.63,"cupboard"],[1375272496000,22.69,"cupboard"],[1375272566000,22.75,"cupboard"],[1375272637000,22.75,"cupboard"],[1375272707000,22.69,"cupboard"],[1375272777000,22.63,"cupboard"],[1375106429000,17.69,"kitchen"],[1375106500000,17.69,"kitchen"],[1375106570000,17.63,"kitchen"],

[code]....

View 1 Replies View Related

Price Changes When Using Decimal Data Type

Dec 18, 2012

I have a column with the data type decimal setup as so..

`cost` decimal(4,2) NOT NULL DEFAULT '0.00',

However when I input a price, say 100.00, next time I look at it it says 99.99.Why is this? I thought 4,2 allowed 4 chars before the decimal & 2 after?

View 3 Replies View Related

Calculation Based On Latest Price Data?

May 4, 2011

I've designed a database..one of the table gets updated everyday with latest market price data through .csv(table A) of 265 companies(it comprise historic data since 2003)...now i've another table that has (table B)numoshare column..currently i'm trying to create a view/sp that will multiply latest price and numofshare.

CREATE TABLE IF NOT EXISTS `price` (
`Ticker` varchar(14) NOT NULL,
`Date` date NOT NULL DEFAULT '0000-00-00',
`Open` decimal(12,2) DEFAULT NULL,
`High` decimal(12,2) DEFAULT NULL,

[code]...

its working but i need to multiply the price.close column with the latest price that was uploaded to the price table.

View 6 Replies View Related

Correct Data Type For Price With Commas And Dots

Nov 5, 2010

I`m very new with PHP and Databases, but I`m setting up a website that shows information about products, and their prices too.

The original information is in CSV files.
The prices have formatted with commas and dots as follow:

12,300.99 -->(thousands separated by commas)
3,500.25 -->(thousands separated by commas)
235.50
etc

My problem is that the prices data is incorrectly stored in MySQL DB when is greater than 999.99. If a price has a comma only is stored the first number as follow:

In CSV file MySQL DB stores
12,300.99----->1.00-->Incorrect
3,500.25------>3.00-->Incorrect
235.50-------->235.50-->Correct

The structure of proPrice Field is:
Field Type Collation Attributes Null Default
prodPrice decimal(10,2) Yes NULL

Which data Type I have to use in order to see the correct price loaded within my DB?

View 6 Replies View Related

Sales Management System - Change Item Price Without Losing Old Data

Feb 1, 2012

I am creating a sales management system, how do i change the price of an item without losing old sales data.

View 2 Replies View Related

What Is Best Data Store For Sorting Large Data

Oct 5, 2012

I have millions (30-100 millions) of records with multiple columns. I need to sort this data on multiple columns and display them in pages. What is the best way to store this data? Let us say if there is a table with columns T(id, c1,c2 ... cn). In MYSQL this query looks something like this:

select id from T order by cx, cy limit 1000, 1000

I can assume it is a read only table, but I need fast response. Is there any advantage for this problem with NoSQL databases?

View 1 Replies View Related

Data Sorting By The SUM

Jan 17, 2013

Trying to make some progress for a baseball-related site of mine. For instance from my tables I want to produce a query that shows the career leaders for a given stat (Games Played in this case) for the team that I specify.

 $query = "SELECT Pos, FName, LName, SUM(G) FROM retired_batters WHERE Team = 'ATL' GROUP BY LName"; $result = mysql_query($query) or die(mysql_error()); 
while($row = mysql_fetch_array($result))[echo $row['Pos']. " ". $row['FName']. " ". $row['LName']. " ". $row['SUM(G)']; echo "<br />"; ]

When I run this I get the info that I'm looking for: Position he played, First name, Last Name, Games he played for ATL (and it's obviously sorted by last name.

However, I want this data to be sorted by the sum (i.e. I want to know the top 25 for this stat) but when I try to GROUP BY or ORDER BY I do something wrong and get weird results.

View 14 Replies View Related

Sorting Order Of Data

Mar 21, 2013

I have limited knowledge of Mysql, but understand the following script starts at the beginning of the DB and will work its way through.

<code>public static function getNextLead()[$lead_res = Database::get()->data->query("SELECT `lead_id` FROM `" . DATABASE_TBL_LEADS . "` WHERE `lead_disposition` IS NULL AND (`lead_timeout` IS NULL OR `lead_timeout` < NOW()) AND (`lead_locked` = '0000-00-00 00:00:00' OR `lead_locked` IS NULL) AND `lead_active` = '1' LIMIT 1");
$lead_num = $lead_res->numRows();if($lead_num == 1)
</code>

I want to access the leads WHERE `lead_disposition` IS NULL AND (`lead_timeout` IS NULL) AND (`lead_locked` IS NULL) AND `lead_callcount` IS '0' AND `lead_active` = '1'and then access the other available leads. I can read code, but do not know the correct syntax, so i am going round round in circles.

View 3 Replies View Related

Sorting Data From Query Using Php?

Oct 25, 2012

This code is used to get a specific list of ID's from one table, then use those ID's to get the information from another table. Once I get all the information from the 2nd table, I am attempting to sort the data alphabetically based on a field in the 2nd table.

Example, I am getting the name based on a correlating ID and then want to display the entire result in alphabetical order by name (artist_name).

Here is the code I have. When I execute this without the sort(), it works fine but is not in alphabetical order. When I add the sort() in the 2nd while statement, the page looks the same but the name and other data do not display. The source code in the browser shows that the results are being accounted for but the sort must be preventing the variables or information from being displayed for some reason.

I haven't used a sort function before and I tried looking at some examples but couldn't really find something specific to my situation.

<?php $counter = 0;
$artistInfo = mysql_query("SELECT DISTINCT event_url_tbl.artist_id FROM event_url_tbl WHERE (SELECT cat_id FROM artist_tbl WHERE artist_tbl.artist_id = event_url_tbl.artist_id) = 1");
while ($aID = mysql_fetch_array($artistInfo))

[code]....

View 2 Replies View Related

Sorting Data By INT Column With 0 At The Bottom

Sep 14, 2010

I have in my database table the integer column and I must the data sorting by this column.
My problem is, that the number in this column are e.g.:
2
0
1
6
0
5
(When asked "... ORDER BY column ASC")

And I need to get the lines from table in the following order:
1
2
5
6
0
0

View 2 Replies View Related

Sorting Varchar Data Numerically

Jul 29, 2010

I have some data which goes like

1900
1899
1898

[Code]....

This is killing my project here and my dean wont accept the project without this.

View 10 Replies View Related

Sorting Data - Search For Every Name Of All Of Category At Once

Jul 23, 2010

I'm starting a new database/community site about a game. It should contain all kinds of information. I want to create a search option that would search for every name of all of the 'category's at once. Also an option to list all the objects of one category.

My question is: What would be the best way to store and index the following data?

Items (Properties: id, name, value, notes, category) Some categories of items also needs additional properties. Should I make another table for that, and how would I link them together? With the id?

Quests (properties: id, name, category1, category2)

Users (Properties: id, name, email, registration date, etc)

NPC (properties: id, name, location)

Newspost (properties: id, title, content)

All 'pages' can also contain comments.

View 2 Replies View Related

Sorting A Data Set With A Group By Statement

May 14, 2013

I am writing a query into a database that tracks the results of athletic competitions. My database has an athletes table:

| id | first_name | last_name | Gender |
| 1 | Sam | Johnson | m |
| 2 | Adam | Jones | m |

and a results table

| id | time | athlete_id
| 1 | 1302 | 1
| 2 | 1420 | 1
| 3 | 1491 | 2
| 4 | 1541 | 2
| 5 | 0 | 1

I want to retrieve all the athletes and only their fastest result. I have a query like this

select a.id as aid, a.`first`, a.`last`, r.`id` as `rid`, min(r.`time`) as `time` FROM athletes a, results r WHERE r.athlete_id=a.id AND r.time > 0 GROUP BY a.id ORDER BY r.time

So far my query does limit the result to the fastest time, but it's not sorting by the time correctly. I also tried adding second reference to the results table

select a.id as aid, a.`first`, a.`last`, r.`id` as `rid`, r.`time` FROM athletes a, results r, results r2 WHERE r.athlete_id=a.id AND r2.athlete_id=a.id AND r.time > 0
r1.time < r2.time ORDER BY r.time

but that caused a out of memory error. The results table has over a million entries and the athletes entry has over 15,000. So the question remains, is there an efficient way of sorting the grouped records or should I have the PHP script remove results as the record set is looped.

View 2 Replies View Related

Php - Sorting Data In Column Table?

Apr 15, 2013

I am trying to sort column data in my table which is generated from mysql. This is how I want my table to be:-

<th><a href='view.php?sort=first_name'>First Name</th>
<th><a href='view.php?sort=last_name'>Last Name</th>

this is my query but I'm getting an error

Parse error: syntax error, unexpected 'if' (T_IF) in C

$result = mysql_query("SELECT * FROM users")
if($_GET['sort'] == 'first_name'){
$result .= "ORDER BY first_name";

[Code]....

View 3 Replies View Related

Adding Sorting And Pagination To Data From Different Database?

May 12, 2011

My task is to take data from different databases and to display them in a single web page.I am working in joomla and i am able to take data from the databases using separate queries for each database.But the problem is that i need to give sorting and pagination to this web page. how can i do this without making any performance issue since all the databases contains a lot of data. Or can i use a single sql query for this ?

View 1 Replies View Related

Java - Sorting Data By Date And Time

Aug 7, 2012

In mysql database i have table say bug_list

tables name: bug_list

id projectId created_date
1 5 2012-06-30 13:30:57
2 5 2012-07-30 10:30:58
3 5 2012-07-30 12:30:44
4 5 2012-07-31 13:30:14
5 5 2012-07-31 14:30:32

and i want to sort it by month(like jan, feb... ) in first result & sort it by weekly(like mon, tue, wen) in second result sort it by hour(like 2hrs range say 8-10am, 10-12pm....) in third result ...

View 2 Replies View Related

Column Sorting Of String Data With Characters Like ()?

Oct 9, 2012

I have a table and it consists varchar data. The data present inside that column is like this.

"bcd"
cde
abcd
'xxx'
(zzz)

Now I want to sort according to alphabetical order. I have tried this query

select my_col from tbl_user order by ltrim(REPLACE(my_col,'"', '')) ASC

Its' output is as follows :

'xxx'
(zzz)
abcd
"bcd"
cde

But my desired output is, means while sorting it has to sort according to alphabetical order whether " present or not.

abcd
"bcd"
cde
'xxx'
(zzz)

How can I achieve this?

View 2 Replies View Related

Sorting - Query Data And Combine Group And Order Results

Mar 26, 2013

I am having trouble getting a query to work effectively which only queries 1 table. Here is an example of the table data:

ID NAME PARENT_ID SORT_ORDER
1 Home NULL 1
2 Contact NULL 3
3 Service NULL 2
4 Service1 3 0
5 Service3 3 2
6 Service2 3 1

What I would like to do is to return the data from this table with results that have a PARENT_ID appearing under the result with that ID, and to have all results then display in their SORT_ORDER. Here is how I would like the query to result the above data:

ID NAME PARENT_ID SORT_ORDER
1 Home NULL 1
3 Service NULL 2
4 Service1 3 0
6 Service2 3 1
5 Service3 3 2
2 Contact Null 3

View 1 Replies View Related

Php - How To Use Price Data In One Table For A Calculation That Is Stored In Another Table

Oct 2, 2012

I'm trying to setup a sort of room reservations system using two tables:

SETUP:

Room price table: Has, prices for a type room a client may want to rent as well as the dates (day of week) they wish to use it. Pricing varies based on day of the week and per room.

I've setup a different table for each room type as each room type carries different pricing for each day of the week. So, There is an Alpha room table, Bravo room, etc. Within Alpha table are headers for the days of the week with pricing pre-entered into the rows.

Client info table: Has the name, address, date of room use, etc data for the specific client.

EXAMPLE:

Alpha-room price table:
Sun = $100; Mon = $200; Tue=$300 and so on.

Bravo-room price table:
Sun = $100; Mon = $200; Tue=$300 and so on.

Client data table:
Client Name; date-of-room-use; address; day_subtotal; grand_total.

QUESTION:

I'm trying to find PHP code that will:

look at the date of room use in the client data table, look up the associated cost for that date in the specific room pricing table, record that unit cost in the day subtotal of the client data table and sum a grand total in the grand total row of the client data table (assuming the room may be used more than one day by the customer).

I know there's something to do with join but I'm finding it difficult to grasp the concept and, if demonstrate using this example, I think I will have a better understanding of how to work this sort of transaction.

View 1 Replies View Related

High Queries Per Second?

Sep 16, 2010

mysql shows 14814.457 queries per second average. Is that possible? The database has been very slow lately. It used to be running fine. Now it goes down very often. What should I do to improve? There are quite some slow queries which I am working on. Is it time to do replication?

View 2 Replies View Related

Variable Marked In Red To Indicate High Value

Nov 19, 2011

Handler_read_rnd_next number improvement question

The mysql variable mentioned has a rather high value, or at least it's marked in red to indicate a high value.

My knowledge is a little foggy around this number, and how indexes (indices?) are used.

If I currently have a query like this:
SELECT `id`,`field2`,`field3`,`field4`,`field5`,`field6` FROM `table_name` WHERE `id` = a number
in which table_name has an index on id

That runs on every page view, no matter if there is a row with that ID, would this be improved if I changed it to something like this? (simplified for this explanation)

if((SELECT count(`id`) from `table_name` WHERE `id` = a number) > 0) {
SELECT `id`,`field2`,`field3`,`field4`,`field5`,`field6` FROM `table_name` WHERE `id` = a number
}

my reasoning is that an explain indicates the count query is using the index (which makes sense - there is only one index - "id")

View 2 Replies View Related

Getting High Traffic With Webserver

Jan 29, 2013

I work for a school system that uses MySQL for the school website databases. It works beautifully until we have snow days. On those days, concurrent traffic increases tremendously which causes MySQL to shutdown. I jumpstart it using services mysql restart, but that's a very short-term fix.

Any of necessary adjustments to make to overcome this issue? Should we be using MySQL Cluster or something different than the basic version of MySQL?

View 3 Replies View Related

CPU Too High After Server Change?

Dec 7, 2011

I did a server migration for DB server of a Wordpress site with same my.cnf file but after web site switch over to new server the CPU usage is at %90+ all the time. OS and MySQL is same and Box has more core CPUs as well.

View 1 Replies View Related

High Scores Database

Oct 10, 2010

I have 18 packs, including 163 different songs. I want my users on a PHPBB based board to be able to use their phpbb account to login and post scores.

The table would consist of this information: Song, Score, Stars, Percentage, Note-Streak, Username, Difficulty, Game, Date, Proof. It would be something similar to what this site is doing.

What would be the easiest way of implementing this

View 2 Replies View Related

What Value Is Considered High Or Inappropriate

Apr 11, 2013

Will like to know at what value is the variable "Innodb_buffer_pool_wait_free" considered high or inappropriate.

View 1 Replies View Related

Continuous High CPU Load

Sep 23, 2012

I have a server we setup for a client which has all of a sudden seen a continued server CPU load for the MySQL user. The server is a typical cPanel setup with Centos 5 32bit running MySQL 5.1.

Here is what cPane shows:

/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server1.urotodaynet.net.err --pid-file=/var/lib/mysql/server1.urotodaynet.net.pid

It hovers around 98.5% load all the time even when there isn't a heavy load on the site.

View 1 Replies View Related

Why CPU Usage Shows High

Feb 17, 2011

I have 69 tables in the system. Data not huge too. No blobs or clobs. I run a process which inserts data into about 8-10 tables simultaneously and also looks for information in about 15-20 tables. When I run top and try to view the MySQL CPU usage it is sometimes as high as 165%. Once I kill the process it does drop to 0%. why the CPU usage shows high?

View 5 Replies View Related

Php - Wordpress High CPU Usage

Sep 4, 2013

I received an email from my hosting provider (hostgator) telling me that: "Your account has been placed under resource restriction! Your account has exceeded our extreme usage threshold for several hours..."

Logs of the CPU usage are provided below:

CPU seconds used in the past hour: 3318.56999999999, 93% CPU

Tue Sep 3 21:01:10 CDT 2013
Running Processes:

user 21172 60.0 0.1 340216 50288 ? RN 21:01 0:00 /usr/bin/php /home1/user/public_html/index.php

Running Queries:
************* 1. row *************
USER: user_db
DB: name_db
STATE: Sorting result
TIME: 0
COMMAND: Query
INFO: SELECT ID, post_title, meta_value FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND post_status='publish' AND post_type='post' AND meta_key='_liked' ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 10

View 2 Replies View Related

Php - What Causing Such High Load On CPU

Apr 25, 2012

My site has just recently run into some issues with High CPU usage from MYSQL. When I check the Parallels Power Panel, it [URL}... --skip-external-locking" is using a high % of the CPU and the site becomes real slow.

I have checked every MySQL query on the entire site and there is nothing that should cause a long term problem like this. When I disable the routing file on the site, the load goes way down (almost 0%). As soon as it is turned back on, up to 100%. I came into work today to find that the server was down because of this.

The db has no more than 200,000 total records split up into 10 tables. I am starting to think that maybe the process(es) aren't finishing (hanging) or something else is wrong. You can load a page or two before the site comes to a stop.

I've added this to the mysql config file:

log-slow-queries = log-slow-queries.log
long_query_time = 5
log-queries-not-using-indexes

Of course, once I added that, the site seems to load a little better and the log file hasn't had anything written to it yet...

View 1 Replies View Related

69 Tables In System - CPU High Usage

Feb 17, 2011

I have 69 tables in the system. Data not huge too. No blobs or clobs. I run a process which inserts data into about 8-10 tables simultaneously and also looks for information in about 15-20 tables. When I run top and try to view the MySQL CPU usage it is sometimes as high as 165%. Once I kill the process it does drop to 0%. Why the CPU usage shows high?

View 1 Replies View Related

Joins With High Number Of Records?

Jun 9, 2010

I'm doing a join between 3 tables. 2 of them have ~300k rows and other have ~5k. The issue is that i'm getting slow performance and this query is taking almost 10 minutes to complete.

Here is a sample:

SELECT ts.* ,
coalesce((ja.id>0), 0) as invalid_address1,
coalesce((ie.id>0), 0) as invalid_address2

[Code]....

Email/rcpt fields are all unique indexes and i use coalesce function to check which emails are present on one of the two joined tables, so i can remove them later.

Also, recommend a book or a complete article about mysql query optimization and/or best practices on database designs for such number of records. I saw some books about it but they are related to Mysql 4 and i'm looking for something more updated.

View 2 Replies View Related

High CPU Usage But No Heavy Query

Feb 7, 2011

When I run "top" command on my Linux it shows that MySQL is taking 400%-500% CPU usage. But when I run 'watch mysqladmin pr" command to see a list of queries being run by MySQL then it shows just 2, 3 queries and that too only taking 2, 3 seconds. The "top" command shows MySQL constantly fluctuating CPU usage like 130%, 200%, 340%, 230%, 400%, 440%, 635%, 335% e.t.c. but "watch mysqladmin pr" command is not showing queries that are taking up CPU usage. How do I check why MySQL is using so much CPU?

View 2 Replies View Related

High Frequency Database Queue

Mar 10, 2011

I'm using a MySQL database as a queue for pages that have been downloaded by a php based crawler. Sets of pages will be added several times per second and pages will be read by a seperate php indexer running as a daemon also several times a second. This would obviously lead to clashes - I guess the obvious solution to this is to use TSQL for the web crawler although this would cause delays for the indexer.

The other issue is the number of database interactions that the indexer would use and the performance lag this would create, I guess that this could be reduced by pulling records out of the db in groups but then memory could be an issue. Another option would be to store the pages in flat files but then I'm not sure how I could avoid issues with both applications trying to access the same file simultaneously. I've also been looking at queue software like beanstalkd.

View 1 Replies View Related

ADVERTISEMENT