Tag Info

New answers tagged

1

First retrieve id and the computed scores, then sort and join the result back to news: SET @line = 0; SET @last = 25; SELECT B.*,titlescore,timescore,textscore FROM ( SELECT id,titlescore,timescore,textscore,combinedscore,(@line:=@line+1) line FROM ( SELECT id, MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore` ...


0

(I'll attempt some out-of-the-box thinking here. Don't hurt me if I'm completely off ;) I assume an essential part of your query execution time is spent on the clause computing the score for the news text. You'll need to verify that for yourself before continuing. In most of the cases, highly relevant articles (the top 25) will generate a decent score ...


5

The CPU time is tiny compared to the amount of actual time. The second time the query runs, it's fast - presumably once the data is in RAM (being one of the few things which benefits from the second run). Sounds to me like the problem isn't SQL, but the disk. Notice the PAGEIOLATCH waits increasing while your query runs. Have a look at what's happening on ...


2

First of all, I have to ask: Why are you doing this? If you have a performance issue, pursue it. However, you only mention that you are seeing scans instead of seeks. Scans are not always bad - they can be the most efficient method of pulling large amounts of data since sequential file access is less costly in I/O terms than random access. The commenters ...


2

Simpler view For this objective: The point of which is to give averages of the numeric columns for each hour .. it seems just as good to truncate to full hours, which is simpler and cheaper: CREATE OR REPLACE VIEW spp.rtprices_hourly AS SELECT date_trunc('hour', "interval") AS hour , node_id , round(avg(rtlmp), 2) AS rtlmp , ...


0

There are multiple reasons for a query to run slowly. May be because of your engine that is not fine tuned, but suppose your engine is well tuned. Begin by printing to a file the query plan of your sql query. Copy the query and open DBACCESS. Run "set explain on" and paste your query and execute it in the "Query language " of DBACCESS utility. Check the ...


0

Percona themselves have written a tool for this very purpose - it's called pt-index-usage and is to be found here. From the DESCRIPTION: This tool connects to a MySQL database server, reads through a query log, and uses EXPLAIN to ask MySQL how it will use each query. When it is finished, it prints out a report on indexes that the queries didn’t ...


3

Assuming that card_no and log_entry have VARCHAR or CHAR type, I would first add an index on (card_no, date, last_entry): ALTER TABLE entry_log ADD INDEX card_no__date__last_entry__ix (card_no, date, last_entry) ; and then use this query: SELECT CONCAT(date, ' ', last_entry) AS LAST_LOG FROM entry_log WHERE card_no = LPAD('2948', 32, '0') ORDER ...


3

Cleanse your data before storing it. Otherwise, INDEXes may be useless. I this particular case, the TRIM function is hiding card_no, making the INDEX on card_no useless. This SELECT would run a lot faster because of the index: SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE card_no = '2948' OK, you don't like the ...



Top 50 recent answers are included