Re: help wit query optimization (cont'd)
Perrin Harkins schrieb:
On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote:
The resultset is paginated on the front end using the
SQL_CALC_FOUND_ROWS functionality...
Usually a bad idea:
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
usually ... but i do not know of any index capable of having FULLTEXT and
'normal' fields in one index
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
AND table_2.shared_id IN (SELECT shared_id FROM table_1_view)
or am i wrong?
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
On Jan 9, 2008 4:33 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: usually ... but i do not know of any index capable of having FULLTEXT and 'normal' fields in one index Does that matter? It would have to be doing a full scan for SQL_CALC_FOUND_ROWS to work out well. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
Perrin Harkins schrieb: On Jan 9, 2008 4:33 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: usually ... but i do not know of any index capable of having FULLTEXT and 'normal' fields in one index Does that matter? yes, as written in the mentioned article the test is only relevant with correct used indexes, but MySQL does not use more than one index, so this query cannot all be done with indexes please correct me if i am wrong -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
On Jan 9, 2008 8:34 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: yes, as written in the mentioned article the test is only relevant with correct used indexes, but MySQL does not use more than one index, so this query cannot all be done with indexes Well, first of all, MySQL 5 does use more than one index, although I'm not sure it can do this with a full text index. http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html Also, there's a lot of room between a full table scan and using every index. Using any index in a way that allows the query to be satisfied without scanning every single row should be enough to make the count query better. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with query optimization
Your biggest problem is probably the subquery/IN your are performing.
You should change that to a join. And I don't know about using
SQL_CALC_FOUND_ROWS in a full text query that's not boolean, and you
shouldn't use it unless you have a LIMIT clause.
SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
JOIN
(SELECT shared_id FROM table_1_view) as table_3 ON
table_2.shared_id=table_3.shared_id
LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
I know the difference doesn't seem that much, but MySQL optimizes it
very differently.
Brent
On Jan 4, 2008, at 5:47 PM, Eben wrote:
Hi,
I have a query that has to run on a full text indexed table with
many millions of records. I'm trying to figure out some
optimizations for it. Here's the general query:
SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
AND table_2.shared_id IN (SELECT shared_id FROM table_1_view)
Some details:
1. table_1.shared_id and table_2.shared_id are indexed
2. table_2.current is an indexed tinyint
3. table_1_view is derived from a query like:
SELECT shared_id
FROM table_1
WHERE some_field LIKE 'some_value%'
table_1 is a relatively small table i.e. 100k records
table_2 is massive with 10 million records
Any ideas or suggestions are appreciated
thanks,
Eben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote: The resultset is paginated on the front end using the SQL_CALC_FOUND_ROWS functionality... Usually a bad idea: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
I've never used this feature before on my application. On Jan 5, 2008 7:06 AM, Perrin Harkins [EMAIL PROTECTED] wrote: On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote: The resultset is paginated on the front end using the SQL_CALC_FOUND_ROWS functionality... Usually a bad idea: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Help with Query Optimization
Kishore,
We use Vbulletin for our message boards, and I have a
query which
takes approximately 1.2 - 1.5 seconds to execute, on a table
('thread')
having around 130,000 rows, it is as follows
SELECT threadid
FROM thread
LEFT JOIN deletionlog
ON ( thread.threadid = deletionlog.primaryid AND TYPE = 'thread' )
WHERE forumid = 98
AND sticky = 0
AND visible = 1
AND deletionlog.primaryid IS NULL
ORDER BY sticky DESC , lastpost DESC
LIMIT 0 , 15
Your formatting of EXPLAIN output is a bit hard to read, but did you
try indexes on
(sticky,lastpost) and (forumid,sticky,visible)? And depending on how
big deletionlog is,
you might move ...
AND deletionlog.primaryid IS NULL
from the WHERE clause to the ON clause, to reduce the number of rows
the query engine
has to write to its temp table.
PB
-
Kishore Jalleda wrote:
Hi All,
We use Vbulletin for our message boards, and I have a query which
takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
having around 130,000 rows, it is as follows
SELECT threadid
FROM thread AS thread
LEFT JOIN deletionlog AS deletionlog ON ( thread.threadid = deletionlog.
primaryid
AND TYPE = 'thread' )
WHERE forumid =98
AND sticky =0
AND visible =1
AND deletionlog.primaryid IS NULL
ORDER BY sticky DESC , lastpost DESC
LIMIT 0 , 15
There is an index on the field 'lastpost' ( Type: INT(10) ) , also Explain
Select gives me this..
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
thread
index
*NULL*
lastpost
4
*NULL*
112783
Using where
1
SIMPLE
deletionlog
eq_ref
PRIMARY
PRIMARY
5
foxbox16.thread.threadid,const
1
Using where; Using index; Not exists
I am no SQL guru so can somebody kindly help me to optimize this query so
that it could run faster , as during peak times this slows the DB down a
lot..
Kishore Jalleda
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query optimization query SUM
Dear Reynier,
You can use JOIN on your both,
The JOIN have to run on the same feilds i.e IDA.
SELECT * FROM carro_de_compras LEFT JOIN os_articulo ON carro_de_compras.IDA
= os_articulo.IDA
This query returns all your users with their articles if any and you can
iterate on it.
but one note:
Use INDEX on both tables. You may encounter problems when your rows grow up.
about the UPDATE query:
UPDATE table SET value=value+1 WHERE id='1'
is enough, use that.
On 2/7/06, Reynier Perez Mira [EMAIL PROTECTED] wrote:
Hi:
I'm develop a simple shopping cart. I have this two tables:
carro_de_compras
--
IDU int(11) NOT NULL
IDA int(11) NOT NULL
CantidadDeArticulos int(11) NOT NULL
os_articulo
--
IDA int(11) NOT NULL auto_increment,
IDC int(11) NOT NULL default '0',
ANombre varchar(200) NOT NULL default '',
ADescripcion text,
ACantidad int(11) NOT NULL default '0',
AImagen varchar(50) default NULL,
IDU int(11) NOT NULL default '0',
APrecio float(6,2) default NULL,
KEY AI_IDA (`IDA`)
Before ask let me explain some things. As you can see in the tables I have
the same field IDU in both tables. So in first(table carro_de_compras) it
means is user ID loged on ecommerce system, the second is the user ID who
upload articles for sale. Something like eBay in wich you can sale and buy
at every time. The arrive the point in wich I need to optimize queries:
PHP Code:
-
$sql = mysql_query(SELECT * FROM carro_de_compras);
$sresultado = mysql_fetch_assoc($sql);
$query = mysql_query(SELECT * FROM os_articulo WHERE
(IDA='.$sresultado['IDA'].'));
while ($record = mysql_fetch_assoc($query)) {
$productos[] = $record;
}
The question for this problem is: exists any way to optimize this query
and leave only in one line? I read in MySQL doc about it and found some
about JOIN but I can't understand how it works. Maybe because I'm cuban and
not understand english as well as I want.
The other questions is how to add some values to a field. For example:
$sql = mysql_query(UPDATE table SET value=value+1 WHERE id='1');
For do this query I do this:
$sql = mysql_query(SELECT value FROM table WHERE id='1');
$result = mysql_query($sql);
$update = mysql_query(UPDATE table SET (value='.$result['value'].' + 1)
WHERE id='1');
So is possible to optimize this query?
Regards
ReynierPM
4to. año Ing. Informática
Usuario registrado de Linux: #310201
*
El programador superhéroe aprende de compartir sus conocimientos.
Es el referente de sus compañeros. Todo el mundo va a preguntarle y él,
secretamente, lo fomenta porque es así como adquiere su legendaria
sabiduría: escuchando ayudando a los demás...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Sincerely,
Hadi Rastgou
a href=http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1;Get
Firefox!/a
