Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL Table having 150000+ rows responding too slow even in phpmyadmin

I am facing strange Slowness some times it says #1205 – Lock wait timeout exceeded; try restarting transaction & in my specific MYSQL table.

I have below table which have id and TBO_code as index as shown below in the structure along with more than 150000 rows data

--
-- Table structure for table `TBO_hotels`
--

CREATE TABLE `TBO_hotels` (
  `id` bigint(20) NOT NULL,
  `TBO_code` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `address` text NOT NULL,
  `Map` varchar(255) NOT NULL,
  `city_code` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `zip_code` varchar(50) NOT NULL,
  `city_name` varchar(255) DEFAULT NULL,
  `rating` varchar(255) DEFAULT NULL,
  `HotelFacilities` text DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `country_code` varchar(255) DEFAULT NULL,
  `featured_image` varchar(255) DEFAULT NULL,
  `images` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `Attractions` text DEFAULT NULL,
  `CheckInTime` text NOT NULL,
  `CheckOutTime` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `TBO_hotels`
--
ALTER TABLE `TBO_hotels`
  ADD PRIMARY KEY (`id`),
  ADD KEY `TBO_code` (`TBO_code`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `TBO_hotels`
--
ALTER TABLE `TBO_hotels`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
COMMIT;

when i otry to search Rows with respect to column city_code (column value use in other city codes main table), it gives respond very slow
even if i just simply browse table or try to sort with phpmyadmin,

below is an example query, I searched the global variable and found nothing strange in global settings aswell.

Even i already have the correct index data but response is slow in that particular table.
please guide me what i am doing wrong in this table?

SELECT *,
(SELECT count(TBO_code) from TBO_hotelswhere city_code =115936
) as TotalCount
FROM TBO_hotels
where city_code = 115936
ORDER BY id ASC
LIMIT 0,50;

I do not understand what i am doing wrong, as all seems correct.
Please can some one guide me?

Tags:

2

Answers


  1. 0

    Something is holding a lock

    At first glance, the problem is because there is no index on city_code. While yes, that is a problem, it shouldn’t be performing that badly. 150,000 rows is not large. The query completes in less than 100ms on my laptop.

    The problem is right in the error message: #1205 - Lock wait timeout exceeded. Something is locking the table, or a row in the table, for a long time.

    With and without an index

    Without an index on city_code, it must search the whole table. First it must scan the whole table find all matching rows and count them (Table scan on test below). Then it will scan the id index (Index scan on test using PRIMARY) until it finds 50 rows with a matching city_code (Filter: (test.city_code = 115936)). Depending on how city codes are distributed, this is potentially the whole table. In my test data, with 450,000 rows it had to search 150050 rows ((actual time=0.0488..51.4 rows=150050 loops=1))

    mysql> explain analyze SELECT *, (SELECT count(*) from test where city_code =115936 ) as TotalCount FROM test where city_code = 115936 ORDER BY id ASC LIMIT 0,50;                                      
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 50 row(s)  (cost=4.53 rows=5) (actual time=51.9..66.1 rows=50 loops=1)
    -> Filter: (test.city_code = 115936)  (cost=4.53 rows=5) (actual time=51.9..66.1 rows=50 loops=1)
        -> Index scan on test using PRIMARY  (cost=4.53 rows=50) (actual time=0.0488..51.4 rows=150050 loops=1)
-> Select #2 (subquery in projection; run only once)
    -> Aggregate: count(0)  (cost=49878 rows=1) (actual time=152..152 rows=1 loops=1)
        -> Filter: (test.city_code = 115936)  (cost=45369 rows=45088) (actual time=39..152 rows=1504 loops=1)
            -> Table scan on test  (cost=45369 rows=450883) (actual time=0.0597..111 rows=451504 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    If we add an index, MySQL doesn’t necessarily use the index well. Here is the plan after adding an index on city_code and optimize table test. It does effectively the same thing, but it now scans the whole city_code index.

    mysql> explain analyze SELECT *, (SELECT count(*) from test where city_code =115936 ) as TotalCount FROM test where city_code = 115936 ORDER BY id ASC LIMIT 0,50;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 50 row(s)  (cost=4.65 rows=5) (actual time=58.1..71.7 rows=50 loops=1)
    -> Filter: (test.city_code = 115936)  (cost=4.65 rows=5) (actual time=58.1..71.7 rows=50 loops=1)
        -> Index scan on test using PRIMARY  (cost=4.65 rows=50) (actual time=0.0393..57.1 rows=150050 loops=1)
-> Select #2 (subquery in projection; run only once)
    -> Aggregate: count(0)  (cost=51052 rows=1) (actual time=149..149 rows=1 loops=1)
        -> Filter: (test.city_code = 115936)  (cost=46530 rows=45216) (actual time=17.3..148 rows=1504 loops=1)
            -> Covering index scan on test using test_city_code_idx  (cost=46530 rows=452155) (actual time=0.0345..106 rows=451504 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 4 warnings (0.22 sec)

    Find the lock

    Perhaps there is a query locking the table, such as a large insert or update. Because this query must scan the whole table, an exclusive lock on any row potentially blocks the whole query. Check your slow query log and check for locks.

    Login or Signup to reply.
  2. 0

    Try following script to see LOCK tables and then UNLOCK it

    SHOW OPEN TABLES WHERE Table LIKE ‘%wp_%’ AND Database LIKE ‘[DB NAME]’ AND In_use > 0;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search