I'm trying to figure out how to optimize a very slow query in MySQL (I didn't design this): SELECT COUNT(*) FROM change_event me WHERE change_event_id > '1212281603783391'; +----------+ | COUNT(*) | +----------+ | 3224022 | +----------+ 1 row in set (1 min 0.16 sec) Comparing that to a full count: select count(*) from change_event; ...
I'm building a MySQL query to determine how many items from each of several categories appear in a given date range. My initial attempt looked like this: select Title, (select count(*) from entries where CategoryID=1 and Date >= @StartDate and Date <= @EndDate) as Cat1, (select count(*) from entries where CategoryID=2 and Da...
I am currently trying to optimize a few queries and scripts, and I wonder if there is a way to cut off a certain percentage of a MySQL Result Set? for example, I have a Query like this: SELECT TrackingID, OpenTime, FirstPoint FROM PointsTable I need the "middle" 74%, ordered by the difference between FirstPointGMT and OpenTimeGMT, wh...
I just had a fairly complex query I was working with and it was taking 8 seconds to run. EXPLAIN was showing a weird table order and my indexes were not all being used even with the FORCE INDEX hint. I came across the STRAIGHT_JOIN join keyword and started replacing some of my INNER JOIN keywords with it. I noticed considerable speed imp...
PHPWiki has a 5 second slow query each time you save a page edit. The query caught often in the "mysql-slow.log" is: INSERT INTO wikiscore SELECT w1.topage, COUNT(*) FROM wikilinks AS w1, wikilinks AS w2 WHERE w2.topage=w1.frompage GROUP BY w1.topage; The current indexes are as follows: table "wikilinks" has a primary index on "...
I've had a bit of a search, but didn't find anything quite like what I'm trying to achieve. Basically, I'm trying to find a similarity between two users' voting habits. I have a table storing each individual vote made, which stores: voteID itemID (the item the vote is attached to) userID (the user who voted) direction (whethe...
I'm working on an application that will be implementing a hex value as a business key (in addition to an auto increment field as primary key) similar to the URL id seen in Gmail. I will be adding a unique constraint to the column and was originally thinking of storing the value as a bigint to get away from searching a varchar field but w...
On a recently launched site, I noticed that, well over the actual heavy queries on the site, the most costly request, out of several million queries, is actually SET NAMES, which clocks over 2.3 seconds on average, while various multi-join union queries are well below 2 seconds. In the end, this places it near the top of the slow query l...
I have a website that is MySQL/PHP. Occasionally, I cannot connect because of Warning: mysql_connect() [function.mysql-connect]: User foo has already more than 'max_user_connections' active connections in /home/foo/public_html/utilities.php I use a discount web host service and can't really prevent it from happening occasionally. (At ...
Our web application basically dynamically generates tables and relations. It also generate indexes on the basic level. We are looking for a MySQL profiler that will be able to suggest indexes. We have come across this two profilers : MYSQL JET PROFILER : will not tell use what index or covering index will do the job. ROT : will not ...
RESOLVED From the developer: the problem was that a previous version of the code was still writing to the table which used manual ids instead of the auto increment. Note to self: always check for other possible locations where the table is written to. We are getting duplicate keys in a table. They are not inserted at the same time (6 h...
Using PHP and MySQL, I have a forum system I'm trying to build. What I want to know is, how can I set it so that when a user reads a forum entry, it shows as read JUST for that user, no matter what forum they are in, until someone else posts on it. Currently, for each thread, I have a table with a PostID, and has the UserID that posted ...
In my forum that I am building, I want to get information about users that make the posts in a thread. For example, a signature, their forum rank, number of posts, etc, from a table, ForumSettings. I have the list of threads in a table with an ID, and I have the posts in a separate table with a threadID column that links to the ThreadLis...
I have a small project I am doing in Python using web.py. It's a name generator, using 4 "parts" of a name (firstname, middlename, anothername, surname). Each part of the name is a collection of entites in a MySQL databse (name_part (id, part, type_id), and name_part_type (id, description)). Basic stuff, I guess. My generator picks a ra...
Hi, I am trying to create a view for the following query. SELECT DISTINCT products.pid AS id, products.pname AS name, products.p_desc AS description, products.p_loc AS location, products.p_uid AS userid, products.isaproduct AS whatisit FROM products UNION SELECT DISTINCT services.s_id AS id...
I'm trying to sort some data by two columns, but it doesn't seem to be working. What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then the article title, then the article date) 50 | This article rocks | Feb 4, 2009 35 | This a...
I have a simple table in MySql whose raison-d'être is to store logs. The table has an autoincremented sequence and all the other columns has zero referential integrity to other tables. There are no unique keys or indexes on any columns. The column with autoincrement is the primary key. Will concurrent INSERTs ever interfere with each ot...
I am trying to output records from a mysql db with simple pagination, however no information is output at all, and no errors are displayed. The error seems to be with the $data query and using mysql_num_rows, as it worked(aside from paginating) before this. Is there some basic error in my logic? <?php $query =''; if (isset($_GET["query...
I'm having a brain-dead moment... I have two tables described by: CREATE TABLE table_a ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE (name)) CREATE TABLE table_b ( id INTEGER PRIMARY KEY AUTO_INCREMENT, a_key INTEGER ...
Hello, I have the following simple mysqli php application, which should work fine. $pk is accepted perfectly and is a valid ARTICLE_NO, and the query works perfectly when executed directly by mysql. I have put output statements after every event and all except tetsing while executes. The while loop is never entered, and I am unsure why....