How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine
Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa'
While executing these queries from front end VB, It takes above 5 secs with 50 thousand records.
How can I improve speed for this kind of queries. Is there any alternation for this command.
View Complete Forum Thread with Replies
Related Forum Messages:
Improve Speed
I need to improve the speed of my ff sql statement but I do not know HOW! I am using mysql in running this. If the records are below a thousand, I have no problem getting the info quickly. But I tried it on 700,000 records, the feedback took 22 secs.In this sql statement, I am currenly accessing the same table but I have to get the rows of certain conditions multiplied to different numbers. SELECT COUNT(IF(code='play',1,NULL))*5+count(IF(code like 'ok%',1,NULL))*2.50 + COUNT(IF(code='prev' OR code='prev_here',1,NULL))*15 + COUNT(IF(code='hello' OR code='hi',1,NULL))*15 + COUNT(IF(code='new',1,NULL))* 2.50 as sum from table;
View Replies !
Improve Query
I have this query that I use for stepping through records in a table, it selects for me the lowest, previous, next and highest record id refs for the table in question given the current record number. However it returns its query on two rows with repeated (and obsolete) data due to the case statement, I pick out the data I require using mysql_result command in PHP, but it would be much nicer if there was a way to only return a one row result with only the data I require. user_id is the autoinc field in table_t $id is the current record number being viewed PHP SELECTmin(user_id) AS First,max(user_id) AS Last, CASE when sign(user_id - '$id') > 0 then min(user_id) else max(user_id) end AS PrevNextFROM table_tWHERE user_id <> '$id'GROUP BY sign(user_id - '$id')ORDER BY sign(user_id - '$id') for completeness my PHP code for extracting the values i need is PHP $first = mysql_result($result,0,0);$prev = mysql_result($result,0,2);$next = mysql_result($result,1,2);$last = mysql_result($result,1,1);
View Replies !
How To Speed Up Mysql Query/php Code
I have a table in a mysql database. with 5 fields and the field ID as the primary key. There are about 220 records in the database and the field 'Mediumimage' contains the file paths for the images to be dispalyed on the page. the PHP code for the query is shown below. Im just wondering why it takes quite a while to process the pictures which are of a small size. $query_handsets = "SELECT Handsets.Manufacturer, Handsets.Model, Handsets.Mediumimage, Handsets.`Handset Url` FROM Handsets WHERE Handsets.Manufacturer LIKE'". mysql_real_escape_string($_GET['manu'])."'"; $hansets = mysql_query($query_hansets, $Mobilecomparsion) or die(mysql_error()); $totalRows_hansets = mysql_num_rows($hansets); <?php $i = 0; while ($row_hansets = mysql_fetch_array($hansets)) { if ($row_hansets['Manufacturer'] != $lastManufacturer) { if ($i == 0) { echo '<img src="images/handsets/logos/'. $row_hansets['Manufacturer'].'.gif" /><br/>' echo "<table cellspacing=ཆ'><tr>"; $lastManufacturer = $row_hansets['Manufacturer']; } else { echo "</tr></table>"; echo '<img src="images/handsets/logos/'. $row_hansets['Manufacturer'].'.gif" /><br/>' echo "<table cellspacing=ཆ'><tr>"; $lastManufacturer = $row_hansets['Manufacturer']; $i = 0; } } elseif ($i % 13 == 0) { echo "</tr><tr>"; } echo "<td align='center'><font face='Ariel' size=Ƈ'>"; echo "<img src='images/4x4.gif' width=ླྀ' height=Ƈ'><br/>"; echo '<img src="images/handsets/'.$row_hansets['Mediumimage'].'" /><br/>' echo $row_hansets['Model']; echo '</td>' $i++; } echo '</tr></table>' ?>
View Replies !
MYSQL Query Speed + Explanations Needed
Sometime I need to delete results set of the some query. Like DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...) But when I have a table with 100k+ records it become a big problem. Script just hangs up forever As I understand, mysql get each id from table `table` and reexamine SELECT? I tried to create temprary table and filled it with select like DELETE FROM table WHERE id IN (SELECT id FROM `some_temp_table`) But its hangs up too... EXPLAIN do not works for the DELETE ... statement, manual do not helped. So, how does this query work "on the low level"? DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...) Any suggestion how can I improve speed for the large tables?
View Replies !
Speed Up This Query
I have a table with just userid,indexid,and imgid and I use this: SELECT count(userid), indexid, count( imgid ) FROM `photoindex` GROUP BY `indexid`; the table has 2 million rows and it says it take 2sec to finish but really, it takes 15 sec after sending the query I think the lag time is due to mysql reading the entire table into a temporary table in order to do the GROUP BY.can anyone help me reduce this query time? is there a SQL setting to increase the memory to speed up this type of query?
View Replies !
Speed This Query Up?
I have a simple query I am running, but it is taking 6 seconds to run. Im not sure what I can do to speed this up. Am I simply SOL or am I not doing something I could be?
View Replies !
Speed Of Query
I was on the PHP forum and they sent me here. I am quite new to working with large databases (approx 1 million records per table) and am having problems with the speed of viewing the data. I have a page that requests all users from a certain department and displays their names etc (this uses a simple 'SELECT first_name etc FROM staff WHERE department = 3' style query). There are around 800,000 staff in the table and so this query can take a long time to run (sometimes only to return 1 record). Is there any way of speeding up this query? Someone mentioned that creating an index on the department field may speed things up but they didn't seem sure, and I don't know how to do this so would only spend time learning if it might make a large difference! Large websites must deal with tables far larger than this and I was wondering how.
View Replies !
Can You See Any Way To Speed This Query Up A Bit?
SELECT SQL_CALC_FOUND_ROWS title, content, URL, id, (MATCH(URL) AGAINST ('$searchQuery' IN BOOLEAN MODE) * 1.5) + (MATCH(title) AGAINST ('$searchQuery' IN BOOLEAN MODE) * 0.8) + (MATCH(content) AGAINST ('$searchQuery' IN BOOLEAN MODE) * 0.5) AS score FROM $searchType WHERE MATCH(title, URL, content) AGAINST ('$searchQuery' IN BOOLEAN MODE) AND content != '' ORDER BY score DESC LIMIT $limit OFFSET $offset
View Replies !
How To Speed Up Select Query
I have a very big table >2GB. My query is as follows: Select JDay, cda_lake.Cell_Num,NH4, NOx, TP, DO, pH from cda_lake, site_cells where Site_Num =1 and site_cells.Cell_Num = cda_lake.Cell_Num and truncate(JDay,0)=152 order by JDay,Cell_Num; It took more 1 min to get 20504 records. Can you tell me what's wrong?
View Replies !
On What Depends Speed Of The Query?
I didnt have too much in common with large databases and i wonder what kind of query is suposed to be complicated. i mean in terms of performance. are there the golden rules like: when you have more then 5 where conditions use union or something? do you think this query could be written in a "faster" way?
View Replies !
Query Vs Logic Speed
I have a website which uses some fairly large tables ( > 300,000 rows ). As you can imagine accessing these tables can often be very slow. I am attempting speed up my sql and was wondering if, using php, it is faster to perform an sql query or approximately 10 lines of logic? What I am trying to do is, instead of querying the table thousands of times and performing my calculation on each small chunk, I would like to try to get the whole table at once, sort it by the ID's and just iterate over it and perform the calculations I need. Would this speed things up? how much if any? and is it worth the effort?
View Replies !
Indexes To Speed Up A Duplicates Query.
OK, say you're trying to find all the non-exact duplicates in a table, and especially do it with a single query, so that the check can be part of a user interface. Then naturally you're going to have a slow query, because it has to compare every row with every other row. You ought to be able to cut out exactly half of the records in the second table, by just saying: SELECT * FROM TABLE LEFT JOIN users dups ON users.created<dups.created But I can't get MySQL to use the index I have on 'created' (same results for integer-primary key, & a string field). My best guess why not is that (according to the manual) "MySQL will not use an index when ... the use of the index would require MySQL to access more than 30% of the rows in the table" - is this the reason? Another option: say that I thought that my duplicates will always have the same letter in their surname, then I could make an index on the first letter of the surname, and make the join just on *that*. This should make the number of rows needed up to 26 times less, right? But exactly how is this done? MySQL won't use my index on the following query: SELECT * FROM TABLE LEFT JOIN users dups ON SUBSTRING(users.name,0,1)=SUBSTRING(dups.name,0,1)
View Replies !
How To Speed Up Query On Table With 10k+ Entries
a table with 10k entries in the moment. The table will increase by approx. 30k entries each year. I have seen, that the large number of entries has slowed down my queries (from <1 sec to over 4 sec). I tried to add an extra field called 'archived' and set it to '1' if I only need the entry for statistical purposes. But with the where-clausel speed doesn't improve. What can I do to increase speed without having to move 'archived' entries to another table?
View Replies !
Testing Speed And Query Cache
I'm trying to test performance on some queries with a million of registers on my table, using various keys. The problem is that the first time i run the query, it takes about 4/5 seconds, but after that it takes about 0.02 seconds. I know that query cache can be flushed with reset query cache, flush query cache, flush tables also modifing some caps at the sql query, but i'll never take 4/5 seconds again. for example: select * from table where id=2 order by index_field desc; 4/5 seconds. select * from table where id=2 order by index_field desc; 0.02 seconds. select * from table where id=3 order by index_field desc; 4/5 seconds. select * from table where id=3 order by index_field desc; 0.02 seconds. etc, etc...
View Replies !
Does Query Speed Depend On The Number Of Coloms?
Does the query speed also depend on the colums that you don't use in the query (so not in the where and not in the select part). I have a lange unused backup field in every row. I only put the backup information into that field once. But never use it on my live database. Does it speed up my queries if I put the backup information into an other table?
View Replies !
Speed Up Query, Order By Column Is Too Slow!
I have a query that takes ages: SELECT tbl1.a, tbl2.b MATCH (tbl1.a) AGAINST ( 'someValue'IN BOOLEAN MODE ) AS score FROM `a` , `b` WHERE tbl2.b = 'someRestriction' ORDER BY score DESC, b.tbl1 DESC LIMIT 20 but because there are thousands of rows it takes ages to do both of the orders, however just : ORDER BY score LIMIT 20 -- is really quick and : ORDER BY b.tbl1 LIMIT 20 -- is really slow is it possible rearrange a constantly updated database so that its naturally ordered by b.tbl1 (so there will be no need to do the ORDER BY b.tbl1 query each time), or are there any other methods to speed this up (putting it all in one table instead of 2, is that significant?)
View Replies !
MySQL Query COUNT
I need to create a sql statement that queries a mysql database for the top x amount of entries that include their name. Basically the site logs everytime they upload a file, and I want to see which users have the top number of uploads. Any ideas? Thanks in advanced to any help. Table: fileid filename submittername submitterip fileinfo filetype date
View Replies !
WHERE COUNT MySQL Query
PHP $queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews GROUP BY soundcard_model ORDER BY value DESC LIMIT 10"; Okay, there's my MySQL query. I have a crappy little review script that lets people review home recording sound cards. This script will display 10 sound cards with the highest average score for overall value. As you can tell, this selects the the company, model, and average overall value rating of sound cards that have been reviewed on my site. It puts them in descending order and limits the results to the top 10. I've found that these results are easily skewed when a particular sound card gets only one review. I don't want a single person to have the power to put a sound card on top of my ratings. So, I want to select sound cards from the database that have had at least 3 votes. While I could come up with a php solution, I'd prefer to use MySQL. I have tried this: PHP $queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews WHERE COUNT(soundcard_model)>2 GROUP BY soundcard_model ORDER BY value DESC "; But I keep getting a "Invalid use of group function" error.
View Replies !
Trying To Get Total Count Plus Count From WHERE Clause In One Query
I am fairly familiar with mysql, have been using it with php for about a year now for my development work. One query I am stuck on is the following. I am doing reporting, and need to find the count from a WHERE clause compared to the total count for that group. For the sake of an example, let's say I have a prize table that contains a complete list of prizes. There is a column to list if the prize has been won or not (prizeUsed - Y or N) and another that has the prize name (prizeName - string). I want the final result to list something like: Total Prizes ____________________ 100 of 850 ......
View Replies !
MySQL Date Range Max Count Query
Code: ----------------------------------------------------- Idstartend ----------------------------------------------------- a2008-09-01 15:012008-09-01 15:04 b2008-09-01 15:022008-09-01 15:09 c2008-09-01 15:122008-09-01 15:15 d2008-09-01 16:112008-09-01 16:23 e2008-09-01 16:192008-09-01 16:25 f2008-09-01 17:522008-09-01 17:59 g2008-09-01 18:182008-09-01 18:22 It shows each time some one logs on, and logs off. The ID is not a userId, just a unique id for the record. I want to be able to query the data so i can create a graph showing concurrent visitors. Either at 10 min, Hourly, Daily or monthly summarys. For example: Code: 2008-09-01 16:10 - 16:202 2008-09-01 17:50 - 18:001 2008-09-01 18:10 - 18:201 The above shows me concurrent visitors within ten minute intervals. The query doesnt have to be that specific, it can just show changes. For example: Code: dateCount ------------------------------------------ 2008-09-01 15:011 2008-09-01 15:022 2008-09-01 15:041 2008-09-01 15:090 2008-09-01 15:121 2008-09-01 15:150 2008-09-01 16:111 2008-09-01 16:192 2008-09-01 16:231 2008-09-01 16:250 etc etc etc...
View Replies !
Order By Count Of Occurrences In 2nd Table Without Nested Query (MySQL 4.0.27 Workaround)
My ISP is using an old version of MySQL, 4.0.27-max-log. I am using a query that runs fine on my test-server (MySQL 5.0.70-log). The main point of this query is to return an ordered list of volunteers for job assignment. One of the metrics is the number appearances in an activity table. I am using a nested query to COUNT the number of appearances during a specific time window to obtain this metric, but this approach does not appear to work in 4.0.27. Is there a simpler or alternative query that will make 4.0.27 happy? SELECT job10.person_id, people.history, job10.history, ( SELECT COUNT( * ) FROM activity WHERE activity.person_id = people.person_id AND (activity.day = '2009-2-1' OR activity.day = DATE_ADD( '2009-2-1', INTERVAL 3 DAY)) ) AS appearances, CONCAT( first_name, ' ', last_name ) AS name FROM duty_sing_am INNER JOIN duty_people USING ( person_id ) WHERE people.active AND job10.willingness > 0 ORDER BY appearances, job10.history, people.history, job10.person_id
View Replies !
Speed Up MySQL
I'm currently running mySQL 5.0 community server on Intel core 2 duo with 4GB and Vista business. I have two database, each of around 10million rows, 5GB in size. Whenever I perform operation on these, it takes a long time (>20min). Is there anyway to speed up the process?
View Replies !
MySQL Speed
I'm using a mySQL database to store all user information, as well as all the images (in blobs.) the site is plenty fast currently, but i'm worried that as it grows, all the database access will slow down a lot. does anyone have any tips or information on speeding all of this up? would caching images on disk be faster than pulling them out of the database? i guess i'm really just asking for general mySQL optimization techniques. anyone?
View Replies !
Improve SELECT Command
I perform a SELECT on my database, but it takes over a minute for every run. I have to run it over 10000 times (with different values in the WHERE), so it takes way too long. A was therefore wondering if I could improve the query speed. Below you find the query. It is based on the ratio between a pixel (pix) vs. the average of its neighbourhood (from geo) in the same table (vgt) based on additional requirements (mgba,mgsc,eco). Code:
View Replies !
Improve The Execution Time
i am new to db2 want to ask questions about the performance of my sql commands for a view based another 3 views the sql commnads are as following: create view b_central_subgroup as select communicator as central_member, project_id as project_id from b_normalized_communicator intersect select initiator as central_member, project_id as project_id from b_normalized_initiator intersect select monospeaker as central_member, project_id as project_id from b_normalized_monospeaker it takes 4.4 seconds to execute this sql. it likes that the time is the summ of the other three views. Can the execution time be reduced through some other methods? If it is possible , then how ?
View Replies !
How To Improve On A Nested Select
I'm working on a simple data import tool, and I need to insert email addresses from table two into table one, if they don't already exist in table one. I figured this was a pretty easy nested select statement, but what I'm doing is getting my site taken off line for exceeding the CPU limit. Here's the SQL I'm using to get the new email addresses: SELECT distinct value, id FROM table_two WHERE name = 'email_address' AND value NOT IN (SELECT DISTINCT subscriber_email FROM table_one) There are about 4600 rows in table one, and 145,000 rows in table two. Does this seem like it would be a burdensome query? I'm not a SQL expert my any means, so is there a better way to go about this? It seemed like a simple one to me. Maybe my web host is just stingy with the CPU time.
View Replies !
Best Way To Improve Performance On Order By?
I hope someone can help me with the following problem. (Note: I will simplify my table structure to the essential) I have two tables, one containing objects and one containing the objects a user has, so basically I have * table_userObjects with PRIMARY KEY idUserObject INDEX ON idObject (not unique) * table_objects with PRIMARY KEY idObject INDEX ON name What I want to optimize is the query which gets the objects for one user and sorts them by name. For example: SELECT * FROM table_userObjects,table_objects WHERE table_userObjects.idObject = table_objects.idObject AND table_userObjects.idUser = 3 ORDER BY table_objects.name LIMIT x,30 The db has around 40000 different objects and the top users have 200000 different items. In this cases it takes around 6 seconds to run the query. If I leave out the orderby no sorting has to be done and the query runs fast as expected. Is there anyway to create an index on table_userObjects, based on the name of the objects from table_objects? Or some other way to speed up this query?
View Replies !
Indexes To Improve Performance
I am looking to add indexes to my database to try and improve performance. Now I heard someone say once that you should add an index to any column that you are planning to filter using in the where part of your statements. So my question is this, in below is an example i have a table that I use to store reports that are generated by the system (i haven't chosen this especially but it has the core element that i user everywhere else). These core elements and columns that i regally in a where clause have '*' next to them. As such if i was following the advice of were to add indexes, i would probably add one to each of these columns. Then there are the other three that have '+', which occasionally i conduct a '%%' (wildcard) search on to help me find a cretin report. given this, plus the index on the primary key almost every table would have an index on it. To me this seems a bit much. So how many is too many?
View Replies !
How Do You Improve The Order By In Queries?
I read somewhere about mysql having to scan the table twice or something with "order by something" this was on mysql.com (as far as I remember) it said something about having it only have to scan it once but it didn't explain how to do it I really want to know, because a 500k row table of mine with a few indexes that cut it down to about 90k per topic is lagging more than I want (okay, so 1.2 seconds average page load on a 8mb connection isn't too bad, but I want to fix this order by stuff and maybe make it 0.9 or 1.0 :P)
View Replies !
Index To Improve Queries With AVG()
Is there any way to create some sort of index that will improve the performance of queries which use the AVG() function. An EXPLAIN statement on the query indicates that no index is being used. There are indexes on the ratings table for ID and ratings but they don't seem to be getting used EXPLAIN SELECT u.username AS username, ur.users_ID AS user_ID, IFNULL((AVG(r.rating))*(COUNT(ur.ratings_ID)),0) AS overall_score FROM ratings AS r, user_ratings AS ur, users AS u WHERE r.ID=ur.ratings_ID AND ur.active='yes' AND u.ID=ur.users_ID AND u.active='yes' GROUP BY ur.users_ID ORDER BY overall_score DESC LIMIT 0,40 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE r ALL PRIMARY 5 Using temporary; Using filesort 1 SIMPLE ur ref rating,users_ID_2,active rating 4 v2.r.ID 239 Using where 1 SIMPLE u eq_ref PRIMARY,active PRIMARY 4 v2.ur.users_ID 1 Using where
View Replies !
Minimum Speed For Mysql
We are trying to use mysql in some very high speed applications. To get an Idea of how fast it could run, we did a test query on a table with one row only (and also a zero row table if that matters). The query was a simple "select * from table". The number that came back was 300-400 microseconds. A similar test directly to memory came back in the nanosecond range (we did not do any locking, etc, it was just a simple insert or get of an element in a one row array). If this number is true, it is too slow for us. I am confused though about why MySQL is so slow. We have also tested using an embedded version of mysql, and the time did not improve. We also made the table a heap table, and put it in memory, but the time still was the same. It seems like this is a lower limit to the time. If mysql is not sufficient for these speeds, could someone suggest another database or library that would work? We will have to read and write from different processes, so it must have locking. Shared memory is not a great solution because we are worried that if we write our locking by hand, the code will be buggy and freeze up.
View Replies !
MySQL Speed Test !!
let me explain, I have a Database with 30,000 rows having a single field of length 100 bytes My current database driver takes 0.339067955017 seconds to search through the entire database and find an entry at last record.
View Replies !
How To Set An Index On Table To Improve The Performance
i've got a table with about 500 mio records. there are 3 col. decimal,decimal,int. my aim is to find data which fits best to the two decimal col. in which way should i set the index for the best performance? at the moment my query needs sth about 5min and it shouldn't need longer than 10 sec. is it possible to improve the performance only by using the correct index.
View Replies !
Limit Records To Improve Performance
I'm facing a performance issue, I'm using JDBC to read rows on a huge mySQL database 1.5 Million rows. The programme basically read rows by sample of 1000 rows. select * from <tbl_name> LIMIT i, i+1000 and print result in a text file. This takes 5 minutes for the first 500000 rows, 10 min for the following 500000 and 18 for the rest. Which seems to me very slow just for reading rows? I wonder 2 things: 1- is this normal for mySQL to take this time? 2- if not, is using LIMIT this way in the SQL would have an effect on mySQL performance? 2- how can I improve this performance. If you think of anything that can improve the performance on a select * from , please let me know.
View Replies !
MySql Replication And Network Speed
We purchased 2 new HP DL385 servers that will be used for MySQL 5 in a replication environment. Server 1 (Master) is connected gigabit to our Cisco 3550 core switch The Cisco 3550 core switch is connected gigabit over fiber to a Cisco 3548 switch in another building. Server 2 (Slave) is connected to the Cisco 3548 on a 100MB switched port. My question is whether or not the 100MB connection is going to be good enough to do replication, or if I have to invest in getting gigabit all the way to the slave server?
View Replies !
How To Speed Up MySQL On A Low Resource Server?
I was wondering, are there any recommended ways to speed MySQL up a bit in low resource environments? See, I'm running a small webserver for kicks on an old Compaq Presario with only 16mb RAM (Yes, I'm aware of the 256mb requirement). MySQL is runnin just fine although obviously it is quite slow. What would you do to speed it up if you absolutely had to? (Besides buying another ram stick >_>)
View Replies !
Select Speed Difference Between MySQL 4 And 5?
Anyone know what the select speed difference is between MySQL 4 and 5, if any? I'm not looking for a specific number since of course it will depend on a whole host of factors, but I'm just wondering if it's "generally known" that 5 is supposed to be generally faster than 4 or not.
View Replies !
MySQL Update Speed With 10K+ Of Rows
I Have table called `users` which have 10K+ rows. Each user have a fields called `gold` and `level`. Now each user should get "pow(1.25, level)*100" gold per/hour and it should divide so that users will get 1 gold per time unit. example: user level is 7 and he should get 1.25^7 * 100 gold = 476 gold per hour (1 gold every 7.5 seconds). Now I have a problem. I can't update all users at once every second because it would create too much load for server (mysql load was 100% if I did it). Even if I would do this update in every 7 seconds, it will be too much load for the server. maybe we can determine user gold amount so: user gold = user gold + (seconds_after_last_update/3600); and do update every prime hour? but what happens the when user wants to spend this gold? My mysql-updateing daemon is running in infinite loop doing 1 action every second.
View Replies !
|
TRACKER
