bravo
Last Activity: Dec 1 '09, 07:12 AM
Joined: Jul 9 '07
Location:
-
Now i dropped index name and added new index on c19 ... now the same query giving the result in 39 sec approx.. time got doubled now.... it didnt worked...any thing else i can try? Also have one question .. what should be the acceptable time for a query like above in same conditions...in mysql? -
Thanks for ur quick response... sorry my mistake .. i changed the fields name before submitting post... the c19 is not varchar its same as c20 and the index is on name not username... i have tried to use c19 as index already but same result.Leave a comment:
-
Select Query
Hi,
i am using mysql version 4.1 and there is a table having 2.5 millionCode:select c19,name,c5,count(*) as count,sum(c13) as cost from TableA where c1 like '%' and c5 like '%' and name like 'bravo' and c19 between '2009-01-01 00:00:00' and '2012-01-01 00:00:00' group by date(c19),name,c5 limit 10000 offset 0
records , the table structure is given below.. if i run above select
query time taken is 19.33 sec... -
Dada , By table crashes i mean that i got the error
The table is marked as crash.. and still i am facing the problem, not very frequently but still i need a solution for that....
actually nobody replied for a long time so i didnt opened this forum
today i saw atleast one response ... thanks for it......Leave a comment:
-
Table Crashing frequently
hi
i am using mysql ver 5.0 , one table is crahsing frequently
i need to know why .. is it becoz of the query iam running
or i have some problem with db ..
what i have done is given below :
1. created a table with a primary key and having one long text field
with max _rows and avg_row_length so as to increase the
4 GB limit of MyISAM.
...Code:CREATE TABLE IF NOT EXISTS smsc_inb
-
cache the result set of query
hi
is it possible to cache the result set of two select query in some sort of cache in mysql and then perform some operations on those cached result set.. -
performance problem with batch update
hi
using mysql 4.1 i wish to update records using batch update but for a batch of 100, records and table having only 5000 records execution time is approx 2.4 sec i want to know whether this time is fine or i can further reduce it with some performance tunning ... the table which i am using is having 19 columns the update is on a single column ....
one more thing when i use the batch update for deletion batch size 100 along with update... -
Thanks for ur reply DADA,
but i am using mysql 4.1 is it possible in that .. or i have to first run a query to check the existence of the record then update or insert.. that will be tooo time consuming but i dont have any choice .. can u suggest me a way...Leave a comment:
-
selective insert and update
hi
how can i insert a record in a table if it doesnt exist already and update the record otherwise ie if record exists then it must be updated and if not exist then inserted.
is it possible through one query... For example :
TableOne
ColumnOne|Colum nTwo|....
jhon |123455|....
now a record jhon,10000 comes since jhon already exist so it must be updated
if steve,45 comes it must be inserted... -
thanks a lot amit it worked perfectly....Leave a comment:
-
problem in updating a table...
hi
i am having a situation where i have to update a whole column and set one value true and rest false For example If the table have two columns Name and Status respectively...
Name Status
aTrue
bFalse
cFalse
and if status of b is set to true now then all other status should be false
aFalse
bTrue
cFalse
is this possible with only runnign one query or... -
capture back button on the IE toolbar..
hi
using javascript i am able to capture the backspace key and also to disable it but it is not working for the back button on the toolbar. i am new to javascript
have searched a lot but didnt got success.... the code i have written is like this
...Code:<script language="JavaScript"> <!-- window.document.onkeydown = Backkeyhandler; function Backkeyhandler() { if (window.event -
Is there any alternative for ROWNUM and ROWID in mysql??
hi ,
i need to know whether there is any alternative method through which i can get the result of the querries like below in my sql...
thanks in adwanceCode:select * from table_name where rownum=1 and rowid not in (select rowid from table_name where rownum<10)
-
Thanks a lot for ur help....
i will try it .....
and update if i will get success some how.... may be some one else
get benifit....Leave a comment:
-
yes every table is indexed on the id column
as id is unique and is same in each table
does this effecting the performance???? ?
also i have one doubt over indexing ....
if in a table updation, insertion,delet ion is done
frequently then does indexing effects the performance
ie will it have some negative impact..
and a humble request for u to reply soon as i dont think anybody...Leave a comment:
-
hi
thanks again
i am not an expert but have tried lot of techniques for optimizing this query but got the same result
even the above query gives result in same time as previous one
one more thing i noticed that if i implement paging then the time increases as a multiple like for first 100 rec 10 secs, next 100 20secs and so on.....
so joins are not going to help me
but using subqueries i am not getting...Leave a comment:
-
this is what i get when i run the above query....
id|select_type | table |type |possible_key| key|key_len|ref |rows|Extra
1 | simple | table1 | All | null | null | null | null | 149292 |
1 | simple | table2 | All | null | null | null | null | 100000 |
1 | simple | table3 | ref | id | id | 25 | host.table2.id | 10 |...Leave a comment:
-
Thanks
as per ur suggestions i tried subquerries but unfortunately still not getting any
fruitfull result.i cant break it into different queries due to some reasons...
i need the result set like
Table1
id | name
1 | a
2 | b
3 | c
Table2
id | time
2 | 0:0
3 | 0:1
Table 3
id | status
3 | true
I need...Leave a comment:
-
how to optimize the join query...??
hi
i need to optimize the join query, which joins three tables
say table1 ,table2 , table3 each having huge volume of records...
the query is as
[CODE=mysql]select table1.id,table 2.time,table3.S tatus
from table1 left join table2
using(id)
left join table3
using (id)
limit 10000[/CODE]
this gives result efficiently if limit is less but problem is with huge data... -
Fetching records from multiple tables
hi
i am having a situation where i need to
use three tables. Fetch records from first
on the basis of those search the second table
if it is found there then tag it true else
search in the third and tag it if it is not there
then tag it false.
right now i am doing it it in linear fashion
ie retirieving value from first then searching it
in second and then in third, it is working fine...
No activity results to display
Leave a comment: