I want to output the most used search items of my classifieds of the last 3 days
I have an extra table where I save all search items together with the day and a counter which increments if the search is used again (and for future use the category page it was used on)
This works but the speed is less than desireable.
Any Ideas on how to improve the performance?
UPDATE:
It seems that copying to temp table is causing the problem- How can i change this?
SQL looks like:
SELECT distinct search_item
FROM `tb_search`
WHERE added >= ( CURDATE() - INTERVAL 3 DAY )
ORDER BY count DESC
LIMIT 0,20
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tb_search range added added 3 NULL 4537 Using index condition; Using temporary; Using filesort...
But it is quite slow, it takes 12 ms of the complete 40ms execution time of the php script so 25%
The table structure (Innodb)
23.000 records
tb_search
Column Type Null Default Links to Comments Media (MIME) type
id (Primary) bigint(20) No
search_item varchar(30) No
category varchar(30) Yes NULL
added date No
count smallint(6) No
Indexes
Keyname Type Unique Packed Column Cardinality Collation Null Comment
PRIMARY BTREE Yes No id 23744 A No
search_item BTREE No No search_item 11872 A No
added BTREE No No added 44 A No
space usage
Data 1.5 MiB
Index 1.9 MiB
Overhead 466.0 MiB <– This is the same for all tables??? optimizing all tables did not help?
Effective -485,048,320 B
Total 3.4 MiB
Is the overhead a problem? How can I get rid of it? Optimizing all tables did not change it.
Total size of db is 86MB
**Detailed profile**
1 Starting 48 µs
2 Checking Permissions 6 µs
3 Opening Tables 14 µs
4 After Opening Tables 13 µs
5 System Lock 5 µs
6 Table Lock 3 µs
7 After Table Lock 5 µs
8 Init 20 µs
9 Optimizing 11 µs
10 Statistics 85 µs
11 Preparing 34 µs
12 Executing 4 µs
13 Creating Tmp Table 24 µs
**14 Copying To Tmp Table 9.7 ms**
15 Sorting Result 218 µs
16 Sending Data 18 µs
17 End 4 µs
18 Removing Tmp Table 9 µs
19 End 5 µs
20 Query End 7 µs
21 Closing Tables 9 µs
22 Freeing Items 8 µs
23 Updating Status 21 µs
24 Cleaning Up 4 µs
Summary by stateDocumentation
State
Total Time
% Time
Calls
ø Time
Starting 48 µs 0.47% 1 48 µs
Checking Permissions 6 µs 0.06% 1 6 µs
Opening Tables 14 µs 0.14% 1 14 µs
After Opening Tables 13 µs 0.13% 1 13 µs
System Lock 5 µs 0.05% 1 5 µs
Table Lock 3 µs 0.03% 1 3 µs
After Table Lock 5 µs 0.05% 1 5 µs
Init 20 µs 0.19% 1 20 µs
Optimizing 11 µs 0.11% 1 11 µs
Statistics 85 µs 0.83% 1 85 µs
Preparing 34 µs 0.33% 1 34 µs
Executing 4 µs 0.04% 1 4 µs
Creating Tmp Table 24 µs 0.23% 1 24 µs
**Copying To Tmp Table 9.7 ms 94.42% 1 9.7 ms**
Sorting Result 218 µs 2.12% 1 218 µs
Sending Data 18 µs 0.17% 1 18 µs
End 4 µs 0.04% 2 2 µs
Removing Tmp Table 9 µs 0.09% 1 9 µs
Query End 7 µs 0.07% 1 7 µs
Closing Tables 9 µs 0.09% 1 9 µs
Freeing Items 8 µs 0.08% 1 8 µs
Updating Status 21 µs 0.20% 1 21 µs
Cleaning Up 4 µs 0.04% 1 4 µs
table structure
-- phpMyAdmin SQL Dump
-- version 5.0.3
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Apr 10, 2021 at 11:03 AM
-- Server version: 5.5.68-MariaDB
-- PHP Version: 7.3.27
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `terraristik_main`
--
-- --------------------------------------------------------
--
-- Table structure for table `tb_search`
--
CREATE TABLE `tb_search` (
`id` bigint(20) UNSIGNED NOT NULL,
`search_item` varchar(30) NOT NULL,
`category` varchar(30) DEFAULT NULL,
`added` date NOT NULL,
`count` smallint(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tb_search`
--
ALTER TABLE `tb_search`
ADD PRIMARY KEY (`id`),
ADD KEY `search_item` (`search_item`),
ADD KEY `added` (`added`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tb_search`
--
ALTER TABLE `tb_search`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2
Answers
SOLUTION
By adding an index for
count
I reduced the time by more than 95% !!! Now the whole operation takes abou 1-1.5 ms.The
copy_to_temp table
is down from 10 ms to 0.5 ms - still using 65% of all time but sufficently fast for my needs.Use for a date three days ago:
Or you can use: